记录数据库ddl操作触发器

create table system.ABC

(LOGIN_USER varchar2(60),

     AUDSID varchar2(60),

     IPADDRESS varchar2(60),

     SCHEMA_USER varchar2(60),

     SCHEMA_OBJECT varchar2(60),

     DDL_TIME date,

     DDL_SQL varchar2(3000))

###############################################################

记录数据库ddl操作触发器

CREATE OR REPLACE TRIGGER tr_trace_ddl

  AFTER ddl ON database

DECLARE

  SQL_TEXT   ORA_NAME_LIST_T;

  STATE_SQL  VARCHAR2(4000); --DDL$TRACE.DDL_SQL%TYPE;

  V_ERR_INFO VARCHAR2(200);

BEGIN

  FOR I IN 1 .. ORA_SQL_TXT(SQL_TEXT) LOOP

    STATE_SQL := STATE_SQL || SQL_TEXT(I);

  END LOOP;

  INSERT INTO SYSTEM.ABC    (LOGIN_USER,

     AUDSID,

     IPADDRESS,

     SCHEMA_USER,

     SCHEMA_OBJECT,

     DDL_TIME,

     DDL_SQL)

  VALUES

    (ORA_LOGIN_USER,

     USERENV('SESSIONID'),

     SYS_CONTEXT('userenv', 'ip_address'),

     ORA_DICT_OBJ_OWNER,

     ORA_DICT_OBJ_NAME,

     SYSDATE,

     STATE_SQL);

EXCEPTION

  WHEN OTHERS THEN

    V_ERR_INFO := SUBSTRB(SQLERRM, 1, 198);

END TR_TRACE_DDL;

CREATE OR REPLACE TRIGGER T_AFTER_LOGON AFTER LOGON ON DATABASE

 BEGIN

 IF USER = 'FONEWAPUSER' THEN

 DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE, TRUE);

 END IF;

 END;

/