oracle:System defined event attributes
 
1. System defined event attributes Following is the list of function (attributes) provided by system. All the functions listed will be generated through catproc.sql at database creation time and are owned by sys. All users can access to the function through public synonym.     
System defined event list:
    ora_client_ip_address,
    ora_database_name,
    ora_des_encrypted_password,
    ora_dict_obj_name,
    ora_dict_obj_name_list,
    ora_dict_obj_owner,
    ora_dict_obj_owner_list,
    ora_dict_obj_type,
    ora_grantee,
    ora_instance_num,
    ora_is_alter_column,
    ora_is_creating_nested_table,
    ora_is_drop_column,
    ora_is_servererror,
    ora_login_user,
    ora_privileges,
    ora_revokee,
    ora_server_error,
    ora_sysevent,
    ora_with_grant_option
2. Type of event
1) Resource Manager event
      STARTUP, SHUTDOWN, SERVERERROR
2) Client Event
      AFTER LOGON, BEFORE LOGOFF,
      BEFORE CREATE, AFTER CREATE,
      BEFORE ALTER, AFTER ALTER,
      BEFORE DROP, AFTER DROP,
      BEFORE ANALYZE, AFTER ANALYZE,
      BEFORE ASSOCIATE STATISTICS,
      AFTER ASSOCIATE STATISTICS,
      BEFORE AUDIT, AFTER AUDIT,
      BEFORE NOAUDIT, AFTER NOAUDIT,
      BEFORE COMMENT, AFTER COMMENT,
      BEFORE CREATE, AFTER CREATE,
      BEFORE DDL, AFTER DDL,
      BEFORE DISASSOCIATE STATISTICS, AFTER DISASSOCIATE STATISTICS,
      BEFORE GRANT, AFTER GRANT,
      BEFORE RENAME, AFTER RENAME,
      BEFORE REVOKE, AFTER REVOKE,
      BEFORE TRUNCATE, AFTER TRUNCATE

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
  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;
posted on 2011-09-07 19:12  星^_^風  阅读(290)  评论(0)  编辑  收藏  举报
Copyright © 2024 星^_^風
Powered by .NET 9.0 on Kubernetes Powered By: 博客园