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

On Fri, 4 Oct 2013 22:18:21 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
>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
>

Great, thank you !
I didn't know about recyclebin$ and this is exactly what I was looking for !

Have a great weekend !
Syltrem Received on Fri Oct 04 2013 - 23:12:49 CEST