Oracle System Events
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.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.
Purpose System Events are built-in functions that capture and return metadata about the execution of  DDL. The object names used on this page ORA_... are the commonly used synonyms for the functions. Note the close parallel between these events and corresponding SYS_CONTEXT calls.
Dependencies
DBMS_STANDARD STANDARD SYS_STUB_FOR_PURITY_ANALYSIS
Data Dictionary Objects {ORACLE_HOME}/rdbms/admin/dbmstrig.sql
Event Table DDL for Demos conn uwclass/uwclass@pdbdev

CREATE TABLE event_log (
database_name    VARCHAR2(50),
client_ipadd     VARCHAR2(15),
encrypt_pwd      VARCHAR2(100),
obj_name         VARCHAR2(30),
obj_owner        VARCHAR2(30),
obj_type         VARCHAR2(20),
instance_num     NUMBER,
server_error_num NUMBER,
server_error_msg VARCHAR2(128),
sql_txt          VARCHAR2(256),
grant_opt        VARCHAR2(64));

CREATE TABLE parent (
person_id NUMBER(5),
last_name VARCHAR2(20));
 
ORA_CLIENT_IP_ADDRESS
Client IP address when protocol is TCP/IP

May not work on single Windows machine: Thus the NVL		 CREATE OR REPLACE FUNCTION client_ip_address RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.client_ip_address;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
  IF (ora_sysevent='LOGON') THEN
    INSERT INTO event_log
    (client_ipadd)
    VALUES
    (NVL(ora_client_ip_address, 'N/A'));
  END IF;
  COMMIT;
END sysevent_trig;
/

TRUNCATE TABLE event_log;

conn uwclass/uwclass@pdbdev

SELECT client_ipadd FROM event_log;
 
ORA_DATABASE_NAME
Database name

Returns VARCHAR2(50)		 CREATE OR REPLACE FUNCTION database_name RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.database_name;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
  INSERT INTO event_log
  (database_name)
  VALUES
  (ora_database_name);
  COMMIT;
END sysevent_trig;
/

TRUNCATE TABLE event_log;

conn uwclass/uwclass@pdbdev

SELECT database_name FROM event_log;
 
ORA_DES_ENCRYPTED_PASSWORD
The DES encrypted password of the user being created or altered CREATE OR REPLACE FUNCTION des_encrypted_password(
user IN VARVCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.des_encrypted_password(user);
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON DATABASE
BEGIN
  INSERT INTO event_log
  (database_name, encrypt_pwd)
  VALUES
  (ora_database_name, ora_des_encrypted_password);
  COMMIT;
END sysevent_trig;
/

TRUNCATE TABLE event_log;

ALTER USER uwclass
IDENTIFIED BY sowhat;

ALTER USER uwclass
IDENTIFIED BY uwclass;

col encrypt_pwd format a40

SELECT database_name, encrypt_pwd FROM event_log;
 
ORA_DICT_OBJ_NAME
Name of the dictionary object on which the DDL operation occurred

Returns VARCHAR2(30)		 CREATE OR REPLACE FUNCTION dictionary_obj_name RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.dictionary_obj_name;
END;
/
DROP TRIGGER sysevent_trig;

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
  INSERT INTO event_log
  (database_name, obj_name)
  VALUES
  (ora_database_name, ora_dict_obj_name);
  COMMIT;
END sysevent_trig;
/

TRUNCATE TABLE event_log;

desc parent

ALTER TABLE parent
ADD (event_when TIMESTAMP(9));

desc parent

SELECT database_name, obj_name FROM event_log;
 
ORA_DICT_OBJ_NAME_LIST
Return the list of object names of objects being modified by the event CREATE OR REPLACE FUNCTION dictionary_obj_name_list
(object_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.dictionary_obj_name_list(object_list);
END;
/
DROP TRIGGER sysevent_trig;

CREATE FUNCTION ftest RETURN VARCHAR2 IS
BEGIN
  RETURN 'ZZYZX';
END ftest;
/

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ASSOCIATE STATISTICS
ON DATABASE
DECLARE
 nlist_t ora_name_list_t;
 x       PLS_INTEGER;
BEGIN
  IF ora_sysevent='ASSOCIATE STATISTICS' THEN
    x := ora_dict_obj_name_list(nlist_t);
  END IF;

  FOR i IN 1 .. x LOOP
    dbms_output.put_line(nlist_t(i));
  END LOOP;
END sysevent_trig;
/

set serveroutput on

ASSOCIATE STATISTICS WITH FUNCTIONS ftest DEFAULT SELECTIVITY 10;
 
ORA_DICT_OBJ_OWNER
Owner of the dictionary object on which the DDL operation occurred

ReturnsVARCHAR2(30)		 CREATE OR REPLACE FUNCTION dictionary_obj_owner RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.dictionary_obj_owner;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
  INSERT INTO event_log
  (obj_owner)
  VALUES
  (ora_dict_obj_owner);
  COMMIT;
END sysevent_trig;
/

desc parent

ALTER TABLE parent
MODIFY (event_when TIMESTAMP(6));

SELECT * FROM event_log;
 
ORA_DICT_OBJ_OWNER_LIST
Return the list of object owners of objects being modified by the event CREATE OR REPLACE FUNCTION dictionary_obj_owner_list
(owner_list out ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.dictionary_obj_owner_list(owner_list);
END;
/

-- ora_dict_obj_name_list is a synonym for dictionary_obj_name_list.
DECLARE
 x       PLS_INTEGER;
 olist_t ora_name_list_t; <- verify this for function
BEGIN
  IF ora_sysevent = 'ASSOCIATE STATISTICS' THEN
    x := ora_dict_obj_owner_list(olist_t);
  END IF;

  FOR i IN 1 .. x LOOP
    dbms_output.put_line(olist_t(i));
  END LOOP;
END;
/
 
ORA_DICT_OBJ_TYPE
Type of the dictionary object on which the DDL operation occurred

Returns VARCHAR2(20)		 CREATE OR REPLACE FUNCTION dictionary_obj_type RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.dictionary_obj_type;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
  INSERT INTO event_log
  (obj_type)
  VALUES
  (ora_dict_obj_type);
  COMMIT;
END sysevent_trig;
/

desc parent

ALTER TABLE parent
MODIFY (event_when DATE);

SELECT * FROM event_log;
 
ORA_GRANTEE
Return the grantees of a GRANT DCL statement CREATE OR REPLACE FUNCTION grantee (user_list OUT ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.grantee(user_list);
END;
/

- ora_dict_obj_name_list is a synonym for dictionary_obj_name_list.
conn sys@pdbdev as sysdba

CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT
ON DATABASE
DECLARE
 g_list dbms_standard.ora_name_list_t;
 n     PLS_INTEGER;
BEGIN
  IF (ora_sysevent = 'GRANT') THEN
    n := ora_grantee(g_list);
  END IF;
  dbms_output.put_line(TO_CHAR(n));
  dbms_output.put_line(g_list(n));
END;
/

set serveroutput on

GRANT select ON uwclass.servers TO PUBLIC;

REVOKE select ON uwclass.servers FROM PUBLIC;
 
ORA_INSTANCE_NUM
Instance number CREATE OR REPLACE FUNCTION instance_num RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.instance_num;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
  INSERT INTO event_log
  (instance_num)
  VALUES
  (ora_instance_num);
  COMMIT;
END sysevent_trig;
/

conn uwclass/uwclass@pdbdev

SELECT * FROM event_log;
 
ORA_IS_ALTER_COLUMN
Returns true if the specified column is altered CREATE OR REPLACE FUNCTION is_alter_column(column_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_alter_column(column_name);
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
BEFORE ALTER
ON SCHEMA
BEGIN
  IF ora_is_alter_column('PERSON_ID') THEN
    RAISE_APPLICATION_ERROR(-20001, 'Primary Key Column Can Not Be Altered');
  END IF;
END sysevent_trig;
/

ALTER TABLE parent
MODIFY (last_name VARCHAR2(25));

ALTER TABLE parent
MODIFY (person_id NUMBER(6));
 
ORA_IS_CREATING_NESTED_TABLE
Returns true if the current event is creating a nested table CREATE OR REPLACE FUNCTION is_creating_nested_table
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_creating_nested_table;
END;
/
IF (ora_sysevent = 'CREATE'
AND ora_dict_obj_type = 'TABLE'
AND ora_is_creating_nested_table) THEN
  INSERT INTO event_tab
  (
  VALUES
  ('A nested table is created');
END IF;
 
ORA_IS_DROP_COLUMN
Returns true if the specified column is dropped CREATE OR REPLACE FUNCTION is_drop_column(column_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_drop_column(column_name);
END;
/
conn uwclass/uwclass@pdbdev

CREATE TABLE t (
col1 DATE,
col2 DATE,
col3 DATE);

set serveroutput on

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
DECLARE
 drop_col BOOLEAN;
BEGIN
  IF (ora_sysevent = 'ALTER'
  AND ora_dict_obj_type = 'TABLE') THEN
    drop_col := ora_is_drop_column('COL2');
  END IF;

  IF drop_col THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

ALTER TABLE t DROP COLUMN col3;
ALTER TABLE t DROP COLUMN col2;
 
ORA_IS_SERVERERROR
Returns TRUE if given error is on error stack CREATE OR REPLACE FUNCTION is_servererror(errno IN BINARY_INTEGER)
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_servererror(errno);
END;
/
set serveroutput on

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER DDL
ON DATABASE
DECLARE
 error_number NUMBER := SQLCODE;
BEGIN
  dbms_output.put_line(TO_CHAR(error_number));
  IF ora_is_servererror(error_number) THEN
    dbms_output.put_line('Oops!: ' || sqlerrm);
  ELSE
    dbms_output.put_line('Ok: ' || sqlerrm);
  END IF;
END sysevent_trig;
/

CREATE TABLE t (col DATE);
 
ORA_LOGIN_USER
Login user name CREATE OR REPLACE FUNCTION login_user RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.login_user;
END;
/
SELECT ora_login_user
FROM dual;
 
ORA_PARTITION_POS
In an INSTEAD OF trigger for CREATE TABLE, the position within the SQL text where you could insert a PARTITION clause CREATE OR REPLACE FUNCTION partition_pos
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.partition_pos;
END;
/
-- retrieve ora_sql_txt into sql_text variable first

n := ora_partition_pos;
new_stmt := SUBSTR(sql_text, 1, n-1) || ' ' || my_partition_clause || ' ' || SUBSTR(sql_text, n));
 
ORA_PRIVILEGE_LIST
Returns the list of privileges being granted by the grantee or the list of privileges revoked from the revokee in the OUT parameter; RETURNs the number of privileges in the RETURN value CREATE OR REPLACE FUNCTION privilege_list(
priv_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.privilege_list(priv_list);
END;
/
IF (ora_sysevent = 'GRANT' OR ora_sysevent = 'REVOKE') THEN
  numpriv := ora_privilege_list(priv_list);
END IF;
 
ORA_REVOKEE
Returns the revokees of a revoke event in the OUT parameter; RETURNs the number of revokees in the RETURN value CREATE OR REPLACE FUNCTION revokee (user_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.revokee(user_list);
END;
/
IF (ora_sysevent = 'REVOKE') THEN
  num_of_users := ora_revokee(user_list);
END IF;
 
ORA_SERVER_ERROR
Given a position (1 for top of stack), it RETURNs the error number at that position on error stack CREATE OR REPLACE FUNCTION server_error (position IN BINARY_INTEGER)
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.server_error(position);
END;
/
CREATE OR REPLACE TRIGGER afterdrop_trig
AFTER DROP
ON DATABASE
BEGIN
  INSERT INTO event_log
  (obj_name, server_error_num)
  VALUES
  (ora_dict_obj_name, ora_server_error(1));
END afterdrop_trig;
/

CREATE TABLE t(
testcol DATE);

DROP TABLE t PURGE;

SELECT * FROM event_log;
 
ORA_SERVER_ERROR_DEPTH
Returns the total number of error messages on the error stack CREATE OR REPLACE FUNCTION server_error_depth
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.server_error_depth;
END;
/
n := ora_server_error_depth;
 
ORA_SERVER_ERROR_MSG
Given a position (1 for top of stack), it RETURNs the error message at that position on error stack CREATE OR REPLACE FUNCTION server_error_msg (position IN BINARY_INTEGER) RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.server_error_msg(position);
END;
/
CREATE OR REPLACE TRIGGER afterdrop_trig
AFTER DROP
ON DATABASE
BEGIN
  INSERT INTO event_log
  (obj_name, server_error_msg)
  VALUES
  (ora_dict_obj_name, NVL(ora_server_error_msg(1),'No Error'));
END afterdrop_trig;
/

CREATE TABLE t(
testcol DATE);

DROP TABLE t PURGE;

SELECT * FROM event_log;
 
ORA_SERVER_ERROR_NUM_PARAMS
Given a position (1 for top of stack), it RETURNs the number of strings that have been substituted into the error message using a format like "%s" CREATE OR REPLACE FUNCTION server_error_num_params (
position IN BINARY_INTEGER) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.server_error_num_params(position);
END;
/
n := ora_server_error_num_params(1);
 
ORA_SERVER_ERROR_PARAM
Given a position (1 for top of stack) and a parameter number, RETURNs the matching substitution value (%s, %d, and so on) in the error message CREATE OR REPLACE FUNCTION server_error_param(
position IN BINARY_INTEGER, param IN BINARY_INTEGER)
RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.server_error_param(position, param);
END;
/
-- for example, the second %s in a message: "Expected %s, found %s"
param := ora_server_error_param(1,2);
 
ORA_SQL_TXT
Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken into multiple PL/SQL table elements

The function RETURN value shows the number of elements are in the PL/SQL table.		 CREATE OR REPLACE FUNCTION sql_txt (sql_text OUT ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.sql_txt(sql_text);
END;
/
CREATE OR REPLACE TRIGGER afterdrop_trig
AFTER DROP
ON DATABASE
DECLARE
 sql_text ora_name_list_t;
 n NUMBER;
BEGIN
  n := ora_sql_txt(sql_text);

  INSERT INTO event_log
  (obj_name, sql_txt)
  VALUES
  (ora_dict_obj_name, 'text of triggering statement: ' || sql_text(n));
END afterdrop_trig;
/

CREATE TABLE t(c DATE);

DROP TABLE t PURGE;

SELECT * FROM event_log;
 
ORA_SYSEVENT
System event firing the trigger: Event name is same as that in the syntax.

Returns a VARCHAR2(20)		 CREATE OR REPLACE FUNCTION sysevent RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.sysevent;
END;
/
See Undocumented Oracle Link At Page Bottom
 
ORA_WITH_GRANT_OPTION
Returns TRUE if the privileges are granted with grant option CREATE OR REPLACE FUNCTION with_grant_option RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.with_grant_option;
END;
/
IF (ora_sysevent = 'GRANT' AND ora_with_grant_option = TRUE) THEN
  INSERT INTO event_log
  (grant_opt)
  VALUES
  ('with grant option');
END IF;
 
SPACE_ERROR_INFO
Returns true if the error is related to an out-of-space condition, and fills in the OUT parameters with information about the object that caused the error space_error_info(
error_number     OUT NUMBER,
error_type       OUT VARCHAR2,
object_owner     OUT VARCHAR2,
table_space_name OUT VARCHAR2,
object_name      OUT VARCHAR2,
sub_object_name  OUT VARCHAR2);
IF (space_error_info(eno,typ,owner,ts,obj, subobj) = TRUE) THEN
  dbms_output.put_line=('The object '|| obj || ' owned by ' || owner || ' has run out of space.');
END IF;

Related Topics
DDL Event Triggers
DBMS_STANDARD
Exception Handling
Resumable Transactions
SYS_CONTEXT
System Event Triggers
Undocumented Oracle