DDL触发器

最新推荐文章于 2024-09-21 10:25:52 发布

yfleng2002

最新推荐文章于 2024-09-21 10:25:52 发布

阅读量1.1k

点赞数

文章标签： sql schema byte login user object

本文链接：https://blog.csdn.net/yfleng2002/article/details/7407467

版权

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;
/