On recycle bin

Article by Rampant author Laurent Schneider

More than one user may wondered who created those BIN$ when they first connected to a 10g database. 

create table lsc_t(x number)
partition by range(x)
(partition LESS_THAN_ONE values less than (1)); 

drop table lsc_t; 

select object_name, subobject_name, created
from user_objects
where object_name like 'BIN$%';
OBJECT_NAME                    SUBOBJECT_NAME  CREATED  
------------------------------ --------------- ---------BIN$bh2VJ6FqFJ3gRAAUT+rFpg==$0 LESS_THAN_ONE   07-JUL-09

Ok, it is quite easy to get rid of it. Either at DROP time with a DROP TABLE LSC_T PURGE or later with PURGE RECYCLEBIN. Most of the objects disappear from USER_OBJECTS when dropped actually. The recyclebin view is called : RECYCLEBIN.

purge recyclebin; 

select object_name, subobject_name, created
from user_objects
where object_name like 'BIN$%'; 

no rows selected. 

select * from recyclebin; 

no rows selected.

So far so good…

Let’s see what’s happening with my primary keys 

purge recyclebin;
create table lsc_t(x number constraint lsc_t_pk primary key);
drop table lsc_t;
select object_name, original_name, type from recyclebin; 

OBJECT_NAME                    ORIGINAL_NAME TYPE
------------------------------ ------------- -----
BIN$bh23ggtBHALgRAAUT+rFpg==$0 LSC_T         TABLE
BIN$bh23ggtAHALgRAAUT+rFpg==$0 LSC_T_PK      INDEX

The primary key index is now in the recycle bin as well.

Let’s recover the recycle bin version : 

flashback table lsc_t to before drop;
select index_name from user_indexes where table_name='LSC_T'; 

INDEX_NAME                
------------------------------
BIN$bh3GgNi1HR3gRAAUT+rFpg==$0

select object_name, original_name, type from recyclebin; 

no rows selected.

So if you cannot exclude BIN$ objects from your dba maintenance scripts, you will need to deal with thoses as they may be recovered indexes!