Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South
and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't
make it this year, check out TidalScale at www.tidalscale.com. Be
sure to click on the Solutions link and look through the Oracle resources.
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.
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
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
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
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
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
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;
ORA_SPACE_ERROR_INFO aka 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;