SQL>
SQL> CREATE TABLE logit
2 ( event VARCHAR2(400) );
Table created.
SQL>
SQL> create or replace trigger tr_log_ddl
2 before create or drop on SCHEMA
3 begin
4 insert into logit values ('Object Name: '|| ora_dict_obj_name
5 || ' Type: ' || ora_dict_obj_type
6 || ' Event: ' || ora_sysevent
7 || ' ' || sysdate) ;
8 end ;
9 /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> create table dummy (col1 varchar2(10) ) ;
Table created.
SQL>
SQL> drop table dummy ;
Table dropped.
SQL>
SQL> Select * from LOGIT;
EVENT
--------------------------------------------------------------------------------
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08
Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08
SQL>
SQL> alter trigger tr_log_ddl disable ;
Trigger altered.
SQL>
SQL> create table dummy (col1 varchar2(10) ) ;
Table created.
SQL>
SQL> select * from logit ;
EVENT
--------------------------------------------------------------------------------
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08
Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08
SQL>
SQL> select object_name, status from user_objects where object_type = 'TRIGGER' ;
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
HRC_ORG_SITE_TRIG
INVALID
CAPTURE_TRACE_FILES
INVALID
WHAT_COLUMN
VALID
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
LOGOFF_LOG_TRIGGER
INVALID
APPLICATION_USERS_IOI
INVALID
STUDENT_TRIG
VALID
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
TR_LOG_DDL
VALID
7 rows selected.
SQL>
SQL> alter trigger tr_log_ddl enable ;
Trigger altered.
SQL>
SQL> drop trigger tr_log_ddl;
Trigger dropped.
SQL>
SQL> drop table logit;
Table dropped.
SQL>