Database Trigger for Capturing all DDL Commands and Storing into a TABLE By Jagat Anand May 27, 2009  digg!     Print     Email to Friend Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.

CREATE TABLE ddl_log (
OS_USER       VARCHAR2(30),
user_name     VARCHAR2(30),
ddl_date      DATE,
ddl_type      VARCHAR2(30),
object_type   VARCHAR2(18),
owner         VARCHAR2(30),
object_name   VARCHAR2(128),
statement varchar2(4000));

Step 2:

Then Create the following trigger, it will automatically capture the statement and then will insert into above table..

CREATE OR REPLACE TRIGGER DDL_AUDITING
AFTER DDL ON DATABASE
DECLARE
 sql_text ora_name_list_t;
 v_stmt   long;
n number;
BEGIN
n := ora_sql_txt(sql_text);
  FOR i IN 1..n
  LOOP
    v_stmt := v_stmt || sql_text(i);
  END LOOP;
  INSERT INTO ddl_log
  (os_user,
user_name, ddl_date, ddl_type,
   object_type, owner,
   object_name,STATEMENT)
  VALUES
  (sys_context(\'USERENV\',\'OS_USER\'),ora_login_user, SYSDATE, ora_sysevent,
   ora_dict_obj_type, ora_dict_obj_owner,
   ora_dict_obj_name,v_stmt);
END DDL_AUDITING;