DDL触发器

1. Create the follow table to record the trace:

CREATE TABLE SYS.DDL$TRACE
(
  LOGIN_USER     VARCHAR2(50 BYTE),
  DDL_TIME       DATE,
  IP_ADDRESS     VARCHAR2(50 BYTE),
  AUDSID         VARCHAR2(50 BYTE),
  SCHEMA_USER    VARCHAR2(20 BYTE),
  SCHEMA_OBJECT  VARCHAR2(50 BYTE),
  DDL_SQL        VARCHAR2(4000 BYTE)
);


2. Create the trigger:

CREATE OR REPLACE TRIGGER SYS.TR_TRACE_DDL
AFTER DDL
ON DATABASE
DECLARE
   sql_text    ora_name_list_t;
   state_sql   ddl$trace.ddl_sql%TYPE;
BEGIN
   FOR i IN 1 .. ora_sql_txt (sql_text)
   LOOP
      state_sql := state_sql || sql_text (i);
   END LOOP;
   INSERT INTO ddl$trace (login_user,
                          ddl_time,
                          ip_address,
                          audsid,
                          schema_user,
                          schema_object,
                          ddl_sql)
        VALUES (ora_login_user,
                SYSDATE,
                SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
                USERENV ('SESSIONID'),                
                Ora_Dict_Obj_Owner,
                Ora_Dict_Obj_Name,
                State_Sql);
END tr_trace_ddl;
/





阅读更多
上一篇ORACLE "desc table"对应的SQL语句
下一篇Oracle Lock相关查询.
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭