Oracle System Event Triggers
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
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 LOGOFF
BEFORE / AFTER SET CONTAINER
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 and the logproc procedure (below) must be built before the trigger will compile
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> TRIGGER <trigger_name>
<trigger_type> ON DATABASE
CALL <procedure_name>
/
conn sys@pdbdev as sysdba
alter session set container=PDBDEV;

CREATE TABLE connection_audit (
login_date DATE,
user_name  VARCHAR2(30));

CREATE OR REPLACE PROCEDURE logproc AUTHID DEFINER IS
BEGIN
  INSERT INTO connection_audit
  (login_date, user_name)
  VALUES
  (SYSDATE, USER);
END logproc;
/


CREATE OR REPLACE TRIGGER logintrig
AFTER LOGON ON DATABASE
CALL logproc
/

conn sh/sh@pdbdev
conn scott/tiger@pdbdev

SELECT *
FROM connection_audit;

DROP TRIGGER logintrig;
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
*/
Create 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 demoat 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_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_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
DDL Event Triggers
Instead Of Trigger
Stored Outlines
System Events
SYS_CONTEXT Function
Table Triggers
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved