trigger_trace

建立一张表，用来记录所有的DDL操作的信息。
 
create table DDL$TRACE
(
   LOGIN_USER    VARCHAR2(30),
   AUDSID        NUMBER,
   MACHINE       VARCHAR2(100),
   IPADDRESS     VARCHAR2(20),
   SCHEMA_USER   VARCHAR2(30),
   SCHEMA_OBJECT VARCHAR2(30),
   DDL_TIME      DATE,
   DDL_SQL       VARCHAR2(4000)
)
创建触发器，监控所有的DDL语句。
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,audsid,machine,ipaddress,
schema_user,schema_object,ddl_time,ddl_sql)
         VALUES(ora_login_user,userenv('SESSIONID'),sys_context('userenv','host'),
         sys_context('userenv','ip_address'),ora_dict_obj_owner,ora_dict_obj_name,SYSDATE,state_sql);
EXCEPTION 
    WHEN OTHERS THEN 
--       sp_write_log('捕获DDL语句异常错误：'||SQLERRM);
null;
END tr_trace_ddl;
/
查询操作日志
select * from sys.ddl$trace

参考
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adgsec02.htm#1009213
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm#1000872