CODEWITHSUNDEEP
oracle-databaseoracle12c
namdt55555
namdt55555

Reputation: 469

oracle - what is table name with prefix is BIN$

I create partition for some Table in Oracle Db (12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)

When i use this command to show partition info, I got TABLE_NAME with prefix "BIN$" (BIN$l6719qYYHz/gUwEAAH/8jQ==$0)

What is the meaning of this table? Why it appeared in my Database? Can I remove it?

please leave any document about it

Thank you so much!

select TABLE_OWNER, TABLE_NAME,PARTITION_NAME, num_rows , SUBPARTITION_COUNT from ALL_TAB_PARTITIONS where TABLE_OWNER like '%TEST%' ;

enter image description here

Upvotes: 2

Views: 3411

Answers (1)

Popeye
Popeye

Reputation: 35900

Table names starting with BIN$ are the tables that are dropped and are in the recycle bin.

DROP TABLE command logically moves the table to the recycle bin by renaming it.

You can see all the recycle bin objects using the following command:

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BU_TABLE         BIN$+9q0Ry8iT1ivyRponKIZ4g==$0 TABLE        2020-02-04:21:32:08

If you don't want the table to be stored in the recycle bin, i.e. You don't want to flashback the table then you can use PURGE option along with DROP TABLE command as:

DROP TABLE your_table PURGE;

Even you can set recycle bin ON/OFF at the session and system level.

-- Session
ALTER SESSION SET recyclebin = OFF;
ALTER SESSION SET recyclebin = ON;

-- System
ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = ON;

If you have nothing to do with the dropped object, means you don't have any plans to flashback already dropped tables which are present in the recycle bin then you can purge the recycle bin objects using one of the following techniques:

PURGE TABLE tablename; -- it will remove the table from recycle bin
PURGE RECYCLEBIN; -- it will remove a current user's recycling bin 
PURGE DBA_RECYCLEBIN; -- it will remove entire recycle bin

Cheers!!

Upvotes: 5

Related Questions