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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 5 Oct 2013 08:03:21 +0200
Message-ID: <524faba7$0$2396$426a74cc_at_news.free.fr>

"Syltrem" <syltremzulu_at_videotron.ca> a écrit dans le message de news: smbu491rip4lgdvmsjrsmcbq234ckkjb5b_at_4ax.com...
|
| Great, thank you !
| I didn't know about recyclebin$ and this is exactly what I was looking
| for !
|
| Have a great weekend !
| Syltrem

Take care that what you see depends on the Oracle version (up to patchset) and waht has been in the database before. What I posted was in 10.2.0.4.
What you see in 11.2.0.1 is:

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

---

    103196

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=103196 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#=103196)
  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=103196 in sys.recyclebin$');  13 for x in (select owner#, type#, original_name from sys.recyclebin$ where obj#=103196)  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=103196 in sys.obj$ OBJ --- 2 73.T
FLAGS - 0 0
Looking for object_id=103196 in sys.recyclebin$

Table dropped.

But if I redo the same thing, I have;

SQL> purge recyclebin;

Recyclebin purged.

SQL> create table t (id int, val varchar2(70));

Table created.

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

---

    106990

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=106990 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#=106990)
  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=106990 in sys.recyclebin$');  13 for x in (select owner#, type#, original_name from sys.recyclebin$ where obj#=106990)  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=106990 in sys.obj$ OBJ --- 2 73.BIN$YCpfSmJrQ6mpMl+FXBPBqg==$0 FLAGS - 80 128
Looking for object_id=106990 in sys.recyclebin$ BIN --- 1 73.T Table dropped.

So you have to check both tables.

Regards
Michel Received on Sat Oct 05 2013 - 08:03:21 CEST