10gRecycleBin.txt
2005-09-06 Bikle
------------------

If you work in an Oracle development environment, you may notice that
a bunch of strangely named segments start piling up as the days go by.

For example,

12:09:18 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$///VubRWWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubRhWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubRtWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubR2WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubR6WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubR8WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSBWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSGWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSLWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSQWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSVWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSbWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubShWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSpWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubSvWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubVQWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubVKWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubVEWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubU+WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubU5WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubU0WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUvWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUqWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUlWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUQWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUSWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUMWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubT3WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubTyWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUdWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubUDWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubTSWqLgNAADuo1F2Q==$0 TABLE
RAILS_SESSION                  TABLE
BIN$///VubX5WqLgNAADuo1F2Q==$0 TABLE
USER_ROLE                      TABLE
BIN$///VubXyWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubXsWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubXmWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubXhWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubXcWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubXXWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubXSWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubXNWqLgNAADuo1F2Q==$0 TABLE
BIN$///VubW4WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubW6WqLgNAADuo1F2Q==$0 TABLE
BIN$///VubW0WqLgNAADuo1F2Q==$0 TABLE

I see so many of these segments above,
it's annoying.

What are they?

They are segments in the 'RECYCLEBIN'.

How do I get rid of them?

Two magic words from sqlplus:

PURGE RECYCLEBIN;

You can find out about them via the
view RECYCLEBIN:

12:31:09 SQL> DESC RECYCLEBIN
 Name                                                 Null?    Type
 ---------------------------------------------------- -------- ------------------------------------
 OBJECT_NAME                                          NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                                 VARCHAR2(32)
 OPERATION                                                     VARCHAR2(9)
 TYPE                                                          VARCHAR2(25)
 TS_NAME                                                       VARCHAR2(30)
 CREATETIME                                                    VARCHAR2(19)
 DROPTIME                                                      VARCHAR2(19)
 DROPSCN                                                       NUMBER
 PARTITION_NAME                                                VARCHAR2(32)
 CAN_UNDROP                                                    VARCHAR2(3)
 CAN_PURGE                                                     VARCHAR2(3)
 RELATED                                              NOT NULL NUMBER
 BASE_OBJECT                                          NOT NULL NUMBER
 PURGE_OBJECT                                         NOT NULL NUMBER
 SPACE                                                         NUMBER

12:31:25 SQL>


A quick demo:


12:25:27 SQL> CREATE TABLE dropme AS SELECT sysdate AS x FROM dual;

Table created.

12:28:50 SQL> DROP TABLE dropme;

Table dropped.

12:29:10 SQL> SELECT object_name,operation,type,createtime,droptime FROM RECYCLEBIN WHERE original_name='DROPME';

OBJECT_NAME                    OPERATION TYPE                      CREATETIME
------------------------------ --------- ------------------------- -------------------
DROPTIME
-------------------
BIN$/89Yofg7Rm3gNAADuo1F2Q==$0 DROP      TABLE                     2005-09-06:12:23:54
2005-09-06:12:24:05

BIN$/89Yofg8Rm3gNAADuo1F2Q==$0 DROP      TABLE                     2005-09-06:12:28:50
2005-09-06:12:29:01


12:30:01 SQL> PURGE RECYCLEBIN;

Recyclebin purged.

12:30:18 SQL> SELECT object_name,operation,type,createtime,droptime FROM RECYCLEBIN WHERE original_name='DROPME';

no rows selected

12:30:29 SQL>