Skip to Main Content
  • Questions
  • I dropped a table in oracle but when i saw the indexes became like 'BIN$...' i rebuild them the state is still VALID

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Moizuddin.

Asked: December 02, 2017 - 11:28 am UTC

Last updated: December 06, 2017 - 1:05 pm UTC

Version: Oracle Database 11gr2

Viewed 1000+ times

You Asked

I dropped the table with cascade option, after importing the table the indexes are there with BIN$... name and the state is VALID. Are they really valid i try to rebuild its rebuilding but name is not changing.

and Connor said...

This is the concept of a "recyclebin" - so that you can resurrect a table

SQL> create table t ( x int );

Table created.

SQL> create index IX on t ( x );

Index created.

SQL>
SQL> select * from user_recyclebin;

no rows selected

SQL>
SQL> drop table t;

Table dropped.

SQL> select * from user_recyclebin;

OBJECT_NAME
----------------------------------------
ORIGINAL_NAME
--------------------------------------------------------------------------------------------------------------------------------
OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN
--------- ------------------------- ------------------------------ ------------------- ------------------- ----------
PARTITION_NAME                 CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ --- --- ---------- ----------- ------------ ----------
BIN$HP551cgiRJmHBauZAtUnsQ==$0
IX
DROP      INDEX                     USERS                          2017-12-03:15:22:42 2017-12-03:15:22:48 1.4816E+13
                               NO  YES     172505      172505       172506          0

BIN$tvHURiyfTZmIrO5K7lgwNA==$0
T
DROP      TABLE                     USERS                          2017-12-03:15:22:42 2017-12-03:15:22:48 1.4816E+13
                               YES YES     172505      172505       172505          0


SQL>



So the index is not on table T but on table "BIN$tvHURiyfTZmIrO5K7lgwNA==$0". So your newly imported table T will have its own indexes.

So there no need to necessarily do anything with the objects in the recyclebin - but if you want to you can do:

SQ:> purge recyclebin


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

system analist

fatih mutlu, December 04, 2017 - 7:44 am UTC

Hello there,
I have a server with 2 processors. It consists of 24 cores. My database version is oracle 11g enterprise. But I can use a maximum of 2 cores. Could this be the cause? thank you.
Connor McDonald
December 06, 2017 - 1:05 pm UTC

Can you tell me how this is related to the original question ?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library