Re: after drop trigger : object neither in dba_objects nor dba_recyclebin

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 4 Oct 2013 22:18:21 +0200
Message-ID: <524f2292$0$2213$426a74cc_at_news.free.fr>

"Syltrem" <syltremzulu_at_videotron.ca> a écrit dans le message de news: jZY2u.95547$rp1.69506_at_en-nntp-13.dc1.easynews.com...
| Hello
|
| 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
| ----------
| 1508751
|
| 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
|
|

SQL> select object_id from user_objects where object_name='T';  OBJECT_ID

---

    119748

1 row selected.

SQL> CREATE OR REPLACE TRIGGER droptrig
  2 after DROP ON DATABASE
  3 begin
  4 dbms_output.put_line('Looking for object_id=119748 in sys.obj$');   5 for x in (select owner#, type#, name,

  6                     to_char(flags,'XXXXXXXX') flags, bitand(flags, 128) dropflg
  7              from sys.obj$ where obj#=119748)
  8    loop
  9      dbms_output.put_line('OBJ --- '||x.type#||' '||x.owner#||'.'||x.name);
 10      dbms_output.put_line('FLAGS - '||x.flags||' '||x.dropflg);

 11 end loop;
 12 dbms_output.put_line('Looking for object_id=119748 in sys.recyclebin$');  13 for x in (select owner#, type#, original_name from sys.recyclebin$ where obj#=119748)  14 loop
 15 dbms_output.put_line('BIN --- '||x.type#||' '||x.owner#||'.'||x.original_name);  16 end loop;
 17 end;
 18 /

Trigger created.

SQL> drop table t;
Looking for object_id=119748 in sys.obj$ Looking for object_id=119748 in sys.recyclebin$ BIN --- 1 50.T Table dropped.

The table is in sys.recyclebin$ not in sys.obj$ so you can see neither see it in dba_objects (as it is not in sys.obj$) nor in dba_recyclebin which does an inner join between sys.recyclebin$ and sys.obj$ (and it is not in this later one).

Now look at the following one:

SQL> flashback table t to before drop;

Flashback complete.

SQL> create index i on t(col);

Index created.

SQL> drop table t;
Looking for object_id=116095 in sys.obj$ OBJ --- 2 50.BIN$/8H8VnWPS4eojhx5c0FuUg==$0 FLAGS - 80 128
Looking for object_id=116095 in sys.recyclebin$ BIN --- 1 50.T2 Table dropped.

The dropped table is in both sys tables but you can't see it in dba_objects as this view excludes the object with flag bit 128 set.

Regards
Michel Received on Fri Oct 04 2013 - 22:18:21 CEST