Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

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!

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks