/* if needed to clean up
DROP TRIGGER trg_ddl_trigger ;
DROP PROCEDURE prc_log_proc ;
DROP TABLE tab_ddl_log PURGE ;
*/

CREATE TABLE tab_ddl_log (
	voperation   VARCHAR2(512),
	vdb_name varchar2(512),
	vlogin_user varchar2(512),
	obj_owner   VARCHAR2(512),
	obj_name VARCHAR2(512),
	obj_type varchar2(512),
	sql_text    VARCHAR2(512),
	attempt_by  VARCHAR2(512),
	attempt_dt  DATE)
PCTFREE 30 PCTUSED 65 INITRANS 37 MAXTRANS 255
STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 5 MAXEXTENTS
512 PCTINCREASE 0 FREELISTS 37) ;

CREATE OR REPLACE PROCEDURE prc_log_proc(vdatabase_name  VARCHAR2,
                                     vdict_obj_type  VARCHAR2,
                                     vluser          VARCHAR2,
                                     vsysevent       VARCHAR2,
                                     vdict_obj_owner VARCHAR2,
                                     vdict_obj_name  VARCHAR2,
                                     v_stmt          VARCHAR2) IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO tab_ddl_log
   VALUES
      (vsysevent,
       vdatabase_name,
       vluser,
       vdict_obj_owner,
       vdict_obj_name,
       vdict_obj_type,
       v_stmt,
       USER,
       SYSDATE);
   COMMIT;
END prc_log_proc;
/

CREATE OR REPLACE TRIGGER trg_ddl_trigger
   AFTER ddl ON DATABASE
DECLARE
   sql_text        ora_name_list_t;
   vdatabase_name  VARCHAR2(512);
   vdict_obj_type  VARCHAR2(512);
   vlogin_user     VARCHAR2(512);
   vsysevent       VARCHAR2(512);
   vdict_obj_owner VARCHAR2(512);
   vdict_obj_name  VARCHAR2(512);
   v_stmt          VARCHAR2(512);
   i               PLS_INTEGER;
   n               PLS_INTEGER;
BEGIN
   /* if needed we can disable DDL activity at instance
     IF oper = 'DROP' THEN
       RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop A Production Table');
     ELSIF oper = 'TRUNCATE' THEN
       RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A Production Table');
     ELSIF  ..
     ..
     END IF;
   */

   n := ora_sql_txt(sql_text);
   FOR i IN 1 .. n LOOP
      v_stmt := v_stmt || sql_text(i);
   END LOOP;

   SELECT ora_database_name,
          ora_dict_obj_type,
          ora_login_user,
          ora_sysevent,
          ora_dict_obj_owner,
          ora_dict_obj_name
     INTO vdatabase_name,
          vdict_obj_type,
          vlogin_user,
          vsysevent,
          vdict_obj_owner,
          vdict_obj_name
     FROM dual;

   prc_log_proc(vdatabase_name,
            vdict_obj_type,
            vlogin_user,
            vsysevent,
            vdict_obj_owner,
            vdict_obj_name,
            v_stmt);
END trg_ddl_trigger;
/

delete from tab_ddl_log ;
commit;
drop table tong purge ;
create table tong nologging as select user usr from dual ;
rename tong to tonguc ;
alter table tonguc modify usr varchar2(512) ;
select * from tab_ddl_log ;

SQL> 

3 rows deleted

Commit complete

Table dropped

Table created

Table renamed

Table altered

VOPERATION                                                                       VDB_NAME                                                                         VLOGIN_USER                                                                      OBJ_OWNER                                                                        OBJ_NAME                                                                         OBJ_TYPE                                                                         SQL_TEXT                                                                         ATTEMPT_BY                                                                       ATTEMPT_DT
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
DROP                                                                             TCELL.TCELLDOMAIN                                                                HR                                                                               HR                                                                               TONG                                                                             TABLE                                                                            drop table tong purge                                                            HR                                                                               04.04.2008
CREATE                                                                           TCELL.TCELLDOMAIN                                                                HR                                                                               HR                                                                               TONG                                                                             TABLE                                                                            create table tong nologging as select user usr from dual                         HR                                                                               04.04.2008
RENAME                                                                           TCELL.TCELLDOMAIN                                                                HR                                                                               HR                                                                               TONG                                                                             TABLE                                                                            rename tong to tonguc                                                            HR                                                                               04.04.2008
ALTER                                                                            TCELL.TCELLDOMAIN                                                                HR                                                                               HR                                                                               TONGUC                                                                           TABLE                                                                            alter table tonguc modify usr varchar2(512)                                      HR                                                                               04.04.2008