on recycle bin

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.

:evil:

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!

3 thoughts on “on recycle bin”

  1. Hi Laurent. Interesting article
    I’ve even used DBMS_METADATA.GET_DDL to find out somehow the “real name” of that index, but it gave only BIN$ name :)
    It’s very interesting, why it doesn’t “rename” automatically that index to its original name :S
    Thanks for sharing Laurent!

  2. “It’s very interesting, why it doesn’t “rename” automatically that index to its original name”

    I think, because someone could create index with the same name while the index is in recycle bin..

  3. you could also do create table t; drop table t; create table t; drop table t; flashback table t to before drop rename to t1; flashback table t to before drop rename to t2; flashback table t to before drop rename to t3;

    However I do not like BIN$ index.

    I would prefer a SYS_ generated name

    
    select index_name, generated from user_indexes where index_name like 'BIN$%';
    INDEX_NAME                     GENERATED
    ------------------------------ ---------
    BIN$biz/RM1lE4XgRAAUT+rFpg==$0 N
    

    I would prefer SYS_001234567;Y for an index…

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>