after drop trigger : object neither in dba_objects nor dba_recyclebin

In an AFTER DROP trigger I'm trying to lookup some info about the object being dropped, but found that the object is not found in any of DBA_RECYCLEBIN, DBA_OBJECTS or OBJ$

Before the DROP (and execution of the trigger) the object is listed in DBA_OBJECTS, and after the trigger (or after the DROP is complete) it is listed in DBA_RECYCLEBIN, but the trigger itself sees nothing at all.

Is there a workaround for the trigger to get more info about the object ? I'd like to get the OBJECT_ID.

Here is a quick example that shows the object is "non-existant" while the trigger runs

SQL> create table popo (aa number);

Table créée.

SQL> select object_id from user_objects where object_name='POPO';

OBJECT_ID

1 ligne sélectionnée.

SQL> CREATE OR REPLACE TRIGGER droptrig
2 after DROP
3 ON DATABASE
4 begin
5 dbms_output.put_line('Looking for object_id=1508751 in dba_objects'); 6 for x in (select owner, object_type, object_name from dba_objects 7 where object_id= 1508751)
8 loop
9 dbms_output.put_line('OBJ ----- ' || x.object_type|| ' '|| x.owner || '.' || x.object_name);
10 end loop;
11
12 dbms_output.put_line('Looking for purge_object=1508751 in dba_recyclebin');
13 for x in (select owner, type, original_name from dba_recyclebin 14 where purge_object= 1508751)
15 loop
16 dbms_output.put_line('BIN ----- ' || x.type|| ' ' || x.owner || '.' || x.original_name);
17 end loop;
18 end;
19 /

Déclencheur créé.

SQL> drop table popo;
Looking for object_id=1508751 in dba_objects Looking for purge_object=1508751 in dba_recyclebin

Table supprimée.

SQL> The messages in the FOR loops don't get printed as the object ID is in none of the views.. Would I run the trigger code in an anonymous block *after* the DROP, it would find it in DBA_RECYCLEBIN. Certainly Oracle has not lost trace of this object at this point (when the trigger runs) , it must be somewhere still in tha data dictionary !? Where can I find it ?

This is part of a trigger that audits table DROPs by developers.

Thank you !
Syltrem Received on Wed Oct 02 2013 - 19:47:15 CEST