SQL>
SQL>
SQL> create table dropped_objects(
2 object_name varchar2(30),
3 object_type varchar2(30),
4 dropped_on date );
Table created.
SQL>
SQL> create or replace
2 trigger log_drop_trigger
3 before drop
4 on scott.schema
5 begin
6 insert into dropped_objects
7 values ( ora_dict_obj_name,
8 ora_dict_obj_type,
9 sysdate );
10 end;
11 /
SQL>
SQL> create table drop_me(x number );
Table created.
SQL>
SQL> create view drop_me_view as select * from drop_me;
View created.
SQL>
SQL> drop view drop_me_view;
View dropped.
SQL>
SQL> drop table drop_me;
Table dropped.
SQL>
SQL> select * from dropped_objects;
no rows selected
SQL>
SQL> drop table dropped_objects;
Table dropped.
SQL>
SQL>