1. Create a table to store DDL information
create table ddl$trace
(login_user varchar2(50),
ddl_time date,
ip_address varchar2(50),
audsid varchar2(50),
schema_user varchar2(20),
schema_object varchar2(50),
ddl_sql varchar2(500));
2. Create a trigger which will be triggered while DDL executed
CREATE OR REPLACE TRIGGER 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,userenv(’SESSIONID’),
sys_context(’USERENV’,’IP_ADDRESS’),
ora_dict_obj_owner,ora_dict_obj_name,state_sql);
END tr_trace_ddl;
The later thing is to query ddl$trace for the DDL detail.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/43132/viewspace-873814/ ，如需转载，请注明出处，否则将追究法律责任。
-
博文量54
-
访问量226707
