Oracle System Event Triggers
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Data Dictionary Objects
ALL_TRIGGERS DBA_TRIGGERS USER_TRIGGERS
CDB_TRIGGERS TRIGGER$  
System Privileges
ADMINISTER DATABASE TRIGGER CREATE ANY TRIGGER DROP ANY TRIGGER
ALTER ANY TRIGGER CREATE TRIGGER  
System Event Trigger Types AFTER CLONE -- may require the "ON PLUGGABLE DATABASE" synax
AFTER DB_ROLE_CHANGE -- for Data Guard failover and switchover
AFTER LOGON
AFTER SERVERERROR (does not trap ...
  • ORA-01403: no data found (this is in the Oracle docs but does not seem to be correct)
  • ORA-01422: exact fetch returns more than requested number of rows
  • ORA-01423: error encountered while checking for extra rows in exact fetch
  • ORA-01034: ORACLE not available
  • ORA-04030: out of process memory when trying to allocate string bytes (string, string)
AFTER STARTUP
AFTER SUSPEND
BEFORE / AFTER SET CONTAINER
BEFORE LOGOFF
BEFORE SHUTDOWN
BEFORE UNPLUG -- may require the "ON PLUGGABLE DATABASE" syntax
Disabling System Triggers If there is an error in a system trigger, for example an AFTER STARTUP trigger, it may be impossible to start the database. The following is the method for disabling system triggers.
conn sys@pdbdev as sysdba

set linesize 150
col NAME format a30
col VALUE format a20
col DESCRIPTION format a60

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';

ALTER SYSTEM SET "_system_trig_enabled" = TRUE SCOPE=BOTH;
 
Create System Event Trigger
System Trigger Demo

Demo table, procedure and trigger showing how the SET CONTAINER system trigger works
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> TRIGGER <trigger_name>
<trigger_type> ON DATABASE
CALL <procedure_name>
/
conn sys@pdbdev as sysdba

CREATE TABLE set_container_audit (
audit_date DATE,
trig_name  VARCHAR2(30),
con_id     NUMBER);

CREATE OR REPLACE PROCEDURE cont_audit(trigName IN VARCHAR2) AUTHID DEFINER IS
BEGIN
  INSERT INTO set_container_audit
  (audit_date, trig_name, con_id)
  VALUES
  (SYSDATE, trigName, SYS_CONTEXT('USERENV', 'CON_ID'));
  COMMIT;
END cont_audit;
/

CREATE OR REPLACE TRIGGER setcont_audit_B
BEFORE SET CONTAINER ON DATABASE
BEGIN
  cont_audit('BEFORE');
END setcont_audit_B;
/

CREATE OR REPLACE TRIGGER
setcont_audit_A
AFTER SET CONTAINER ON DATABASE
BEGIN
  cont_audit('AFTER');
END setcont_audit_A;
/

ALTER SESSION SET CONTAINER=PDB$SEED;
-- pause 5 seconds for demo purposes
ALTER SESSION SET CONTAINER=CDB$ROOT;

SELECT * FROM set_container_audit;

DROP PROCEDURE cont_audit;
DROP TABLE set_container_audit PURGE;
Demo Application To Log Logon Attempts ... Both Successful And Failed CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> TRIGGER <trigger_name>
<trigger_type> ON DATABASE
  <trigger_code>
END <trigger_name>;
/
TRUNCATE table connection_audit;

-- trigger to trap successful logons
CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO connection_audit
  (login_date, user_name)
  VALUES
  (SYSDATE, USER);
END logon_audit;
/

conn scott/tiger@pdbdev
conn sh/sh@pdbdev
conn sys@pdbdev as sysdba
conn uwclass/uwclass@pdbdev

SELECT *
FROM connection_audit;

-- trigger to trap unsuccessful logons
CREATE OR REPLACE TRIGGER logon_failures
AFTER SERVERERROR
ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO connection_audit
    (login_date, user_name)
    VALUES
    (SYSDATE, 'ORA-1017');
  END IF;
END logon_failures;
/

conn scott/tigre@pdbdev
conn abc/def@pdbdev
conn test/test@pdbdev

conn uwclass/uwclass@pdbdev

SELECT *
FROM connection_audit;

/*
other errors that could be trapped include:
ORA-01004 - default username feature not supported
ORA-01005 - null password given
ORA-01035 - Oracle only available to users with restricted session priv
ORA-01045 - create session privilege not granted
*/
Instead of Create Trigger
Replaces a CREATE statement's code with a call to a PL/SQL function, procedure, package, or alternative code.

In the demo at right an attempt to create any object will result in creating a table named reports.
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> TRIGGER <trigger_name>
INSTEAD OF CREATE ON SCHEMA
  <trigger_code>
END <trigger_name>;
/
conn sys@pdbdev as sysdba

GRANT create table TO scott; -- must be explicit not through a role

conn scott/tiger@pdbdev

SELECT table_name
FROM user_all_tables
ORDER BY 1;

TABLE_NAME
------------------------------
BONUS
DEPT
EMP
SALGRADE

CREATE OR REPLACE TRIGGER ioct
INSTEAD OF CREATE ON SCHEMA
BEGIN
  execute immediate 'CREATE TABLE scott.reports AS SELECT * FROM scott.emp WHERE 1=2';
END;
/

CREATE TABLE zzyzx (
pid   NUMBER(8),
pname VARCHAR2(20));

SELECT table_name
FROM user_all_tables
ORDER BY 1;

TABLE_NAME
------------------------------
BONUS
DEPT
EMP
REPORTS
SALGRADE
 
Drop System Event Trigger
Drop Trigger DROP TRIGGER <trigger_name>;
conn sys@pdbdev as sysdba

ALTER SESSION SET CONTAINER=PDBDEV;

DROP TRIGGER logon_failures;
 
Demos
Start all PDBs startup conn sys@pdbdev as sysdba

CREATE OR REPLACE TRIGGER start_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
  execute immediate 'ALTER PLUGGABLE DATABASE ALL OPEN';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END start_all_pdbs;
/

shutdown immediate;
startup;

SELECT name, open_mode
FROM v$pdbs;
Start selected PDBs at startup conn sys@pdbdev as sysdba

CREATE TABLE startup_pdbs (
pdb_name VARCHAR2(30),
start_flag  VARCHAR2(1));

INSERT INTO startup_pdbs
(pdb_name, start_flag)
VALUES
('PDBDEV', 'Y');

INSERT INTO startup_pdbs
(pdb_name, start_flag)
VALUES
('ORCL', 'N');

COMMIT;

CREATE OR REPLACE TRIGGER pdb_start_trig
AFTER STARTUP
ON DATABASE
BEGIN
  FOR r IN (SELECT pdb_name FROM startup_pdbs WHERE start_flag = 'Y') LOOP
    EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ' || r.pdb_name || ' OPEN';
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END pdb_start_trig;
/

shutdown immediate;
startup;

SELECT name, open_mode
FROM v$pdbs;
Log System Errors CREATE TABLE servererror_log (
error_datetime TIMESTAMP,
error_user     VARCHAR2(30),
db_name        VARCHAR2(9),
error_stack    VARCHAR2(2000),
captured_sql   VARCHAR2(1000));

CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
 captured_sql VARCHAR2(1000);
BEGIN
  SELECT q.sql_text
  INTO captured_sql
  FROM gv$sql q, gv$sql_cursor c, gv$session s
  WHERE s.audsid = audsid
  AND s.prev_sql_addr = q.address
  AND q.address = c.parent_handle;

  INSERT INTO servererror_log
  (error_datetime, error_user, db_name,
   error_stack, captured_sql)
  VALUES
  (systimestamp, sys.login_user, sys.database_name,
  dbms_utility.format_error_stack, captured_sql);
END log_server_errors;
/
After Logon Trigger for Tracing CREATE OR REPLACE TRIGGER trace_trig
AFTER LOGON
ON DATABASE

DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
  IF (USER = 'UWCLASS') THEN
    execute immediate sqlstr;
  END IF;
END trace_trig;

/
After Logon Trigger for Outlines CREATE OR REPLACE TRIGGER trace_trig
AFTER LOGON
ON DATABASE

DECLARE
 sqlstr VARCHAR2(200) := 'alter session set use_stored_outlines = uw_outlines';
BEGIN
  IF (USER = 'UWCLASS') THEN
    execute immediate sqlstr;
  END IF;
END trace_trig;
/
 
Related Queries
Database Level Event Triggers SELECT a.obj#, a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 0;
Schema Level Event Triggers SELECT a.obj#, a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 88;

Related Topics
Built-in Functions
Built-in Packages
Database Security
DDL Event Triggers
Instead Of Trigger
Stored Outlines
System Events
SYS_CONTEXT Function
Table Triggers
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx