In the database, it may sometimes be necessary to disable system triggers during operations such as java installation, downgrade, or upgrade. An error in a system trigger that we create can cause serious problems in the database. In these cases, system triggers may need to be disabled.
To disable system triggers, the value of parameter _system_trig_enabled must be set to false. We can learn the current value of this parameter with the following query.
1 2 3 4 5 6 | SELECT x.ksppinm name, y.ksppstvl VALUE, ksppdesc description FROM x$ksppi x, x$ksppcv y WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm = '_system_trig_enabled' |
We can change the parameter as follows.
1 2 3 | SQL> alter system set "_system_trig_enabled"=false scope=both sid='*'; System altered. |
You can use the following query to find out which triggers will be disabled.
1 2 3 | SELECT a.obj#, a.sys_evts, b.name FROM trigger$ a, obj$ b WHERE a.sys_evts > 0 AND a.obj# = b.obj#; |
The events that the parameter will affect are as follows:
Resource Manager Events
- STARTUP
- SHUTDOWN
- SERVERERROR
Client Events
- AFTER LOGON
- BEFORE LOGOFF
- BEFORE CREATE
- AFTER CREATE
- BEFORE ALTER
- AFTER ALTER
- BEFORE DROP
- AFTER DROP
- BEFORE ANALYZE
- AFTER ANALYZE
- BEFORE ASSOCIATE STATISTICS
- AFTER ASSOCIATE STATISTICS
- BEFORE AUDIT
- AFTER AUDIT
- BEFORE NOAUDIT
- AFTER NOAUDIT
- BEFORE COMMENT
- AFTER COMMENT
- BEFORE CREATE
- AFTER CREATE
- BEFORE DDL
- AFTER DDL
- BEFORE DISASSOCIATE STATISTICS
- AFTER DISASSOCIATE STATISTICS
- BEFORE GRANT
- AFTER GRANT
- BEFORE RENAME
- AFTER RENAME
- BEFORE REVOKE
- AFTER REVOKE
- BEFORE TRUNCATE
- AFTER TRUNCATE