CODEWITHSUNDEEP
oracle-database
Olivier Faucheux
Olivier Faucheux

Reputation: 2606

BIN$ tables without "recycle bin"

My Oracle Database (10.2) contains tables in Form "BIN$". They are listed by 

select * from user_recyclebin
but not by 
show recycle_bin

The command "show parameters" indicate "recyclebin=on" (it is also the default value in Oracle).

Moreover: Oracle put a table in the recycle bin only if it is member of a cluster, isn't it? (I understand the Oracle Administrator Guide so...). I indeed have dropped the corresponding tables, but I never have used any cluster in this database...

A call to PURGE TABLE has worked: the entry disappeared of user_recyclebin. But why did I have it?

Upvotes: 0

Views: 3060

Answers (2)

Olivier Faucheux
Olivier Faucheux

Reputation: 46

Ok, I didn't understand the SQL statement reference first:

If you drop a table that is part of a cluster, the table is moved to the recycle bin. However, if you subsequently drop the cluster, then the table is purged from the recycle bin and can no longer be recovered with a FLASHBACK TABLE operation.

All tables are put in the bin, the normal ones as well as the cluster ones. The difference is, that the recycle tables of a cluster disapper if the cluster is dropped.

Upvotes: 3

Colin 't Hart
Colin 't Hart

Reputation: 7729

Dropped tables go to the recyclebin except for members of a cluster!

See http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#ADMIN01511

How did you drop your tables?

The command

drop table x;

will cause the table to be moved to the recyclebin, while

drop table x purge;

will cause the table to be dropped immediately.

Upvotes: 1

Related Questions