How To Disable System Triggers in Oracle

dbtut

5 years ago

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.

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'

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.

SQL> alter system set "_system_trig_enabled"=false scope=both sid='*';

System altered.

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.

SELECT a.obj#, a.sys_evts, b.name
  FROM trigger$ a, obj$ b
 WHERE a.sys_evts > 0 AND a.obj# = b.obj#;

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