关闭

DDL触发器

标签: sqlschemabyteloginuserobject
921人阅读 评论(0) 收藏 举报

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





0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:454727次
    • 积分:5449
    • 等级:
    • 排名:第4883名
    • 原创:127篇
    • 转载:39篇
    • 译文:0篇
    • 评论:23条
    文章分类
    最新评论