Oracle System Events
Version 11.2.0.3

General Information
Note: the object names used on this page ORA_... are the commonly used synonyms for the functions
Data Dictionary Objects {ORACLE_HOME}/rdbms/admin/dbmstrig.sql
 
Event Table DDL conn uwclass/uwclass

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

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

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

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

Returns VARCHAR2(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 / 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

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

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;
/
INSERT INTO event_table
(?)
VALUES
('1st error ' || ora_server_error(1));
 
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;
/
INSERT INTO event_table
(?)
VALUES
('1st err msg' || ora_server_error_msg(1));
 
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;
/
DECLARE
 sql_text ora_name_list_t;
 v_stmt   VARCHAR2(2000);
BEGIN
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
    v_stmt := v_stmt || sql_text(i);
  END LOOP;

  INSERT INTO event_table
  (?)
  VALUES
  ('text of triggering statement: ' || v_stmt);
  COMMIT;
END;
/
 
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 Reference
 
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_table
  (?)
  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

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