DDL DROP Monitoring via Oracle Database Trigger
This trigger will monitor DDL, specifically DROP statements and log the schema (user) performing the drop into a table called DDL_LOG. A test script is presented below. Before you use this in production make sure you think through the location of the log table, and size it appropriately. If the trigger fails to fire there can be complications, so make sure you test this and understand it before you deploy it outside of a test instance.
conn / as sysdba create user abc identified by abc; CREATE TABLE ddl_log ( operation VARCHAR2(30), obj_owner VARCHAR2(30), object_name VARCHAR2(30), sql_text VARCHAR2(64), attempt_by VARCHAR2(30), attempt_dt DATE); CREATE OR REPLACE TRIGGER bds_trigger BEFORE DROP ON DATABASE DECLARE oper system.ddl_log.operation%TYPE; BEGIN INSERT INTO system.ddl_log SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE FROM dual; END bds_trigger; / -- now let's test the logging mechanism select * from system.ddl_log; drop user abc; select * from system.ddl_log; select * from DBA_USERS where lower(username) like lower('te%');