General |
Source |
{ORACLE_HOME}/rdbms/admin/dbmstrig.sql |
|
Event Table DDL |
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;
END sysevent_trig;
/
TRUNCATE TABLE event_log;
conn uwclass/uwclass
SELECT client_ipadd FROM event_log; |
|
ORA_DATABASE_NAME |
Database name
Returns a 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);
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);
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 a 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);
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 a 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);
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 a 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);
END sysevent_trig;
/
desc parent
ALTER TABLE parent
MODIFY (event_when DATE);
SELECT * FROM event_log; |
|
ORA_GRANTEE |
Return the grantees of a grant event |
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. |
CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT
ON DATABASE
DECLARE
glist 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));
END;
/
set serveroutput on
GRANT select ON servers TO PUBLIC;
REVOKE select ON 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);
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;
/ |
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; |