RECYCLE BIN IN DATABASE

Introduction: -

We always hear the word 'Recycle Bin' in Windows but I never knew that such a word exists in database too till the time I first read it .My first question was has this something to do with Windows Recycle Bin! But the answer is no.

Recycle Bin in database is actually a data dictionary table, which contains information about the dropped objects. So if you have ever dropped a table by mistake and wished you could undo that, recycle bin is to your rescue. When you drop a table, the database does not immediately remove the space associated with the table but instead it renames the table and places it and any associated objects in a recycle bin.

We can enable or disable the recycle bin using the initialization parameter recyclebin.

 

SQL> show parameter bin;



NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------------

recyclebin                           string      on

We can set/reset it for a particular session/system by using: -

Alter system set recyclebin=on/off
Alter session set recyclebin=on/off


Using Recycle Bin: -

A user can view his objects in the recycle bin by: -

 

SQL> select * from recyclebin;

No rows selected

Currently, the recyclebin for the user is empty.


Now lets create a table: -

 

SQL> create table test (a number);



Table created.



SQL> select * from recyclebin;



no rows selected

Now lets drop the table, which we created just now: -

 

SQL> drop table test;



Table dropped.

Lets peep into the recycle bin now,

 

SQL> select object_name, original_name, operation from recyclebin;



OBJECT_NAME                                   ORIGINAL_NAME               OPERATION

------------------------------ -------------------------------- ---------

BIN$VLKNkYA8S/arE/fy6G6LCg==$0      TEST                             DROP

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names so as to avoid name conflicts that may arise if multiple tables have the same name. Like if User A drops a table, re-creates it with the same name, then drops it again, there will be naming conflicts in the recyclebin or if two users have tables with the same name, and both users drop their tables.
The renaming convention, which the recyclebin follows, is: -
BIN$unique_id$version
Where:
Unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
Version is a version number assigned by the database


We can query the object in the recycle bin in the same way we query other similar objects.

 

	SQL> select * from BIN$VLKNkYA8S/arE/fy6G6LCg==$0;

	No rows selected.

Removing from RecycleBin: -

This actually deals with two cases: -
1. We want to remove what’s their in recyclebin
2. We don’t want some particular tables to go to recycle bin, instead directly deleted

In the first case, we can use the PURGE command to remove the entries from the recyclebin. If we want to remove a particular table and all its associated entries, we can use: -

 

SQL> purge table test;



Table purged.



SQL> show recyclebin;

Here in purge table, we can specify the original name of the table or system generated names; the effect remains same for both the commands.

If we want to delete all the entries belonging to particular tablespace; we can use: -

 

	SQL> purge tablespace <>;

And if we want to empty the complete recyclebin,

 	

	SQL> purge recyclebin;

You cannot roll back a PURGE statement, nor can you recover an object after it is purged


In other case, while dropping the table; we can use the purge clause so that it is directly deleted and space is immediately free.

 

SQL> show recyclebin;



SQL> drop table test purge;

Table dropped.



SQL> show recyclebin;

Restoring from Recyclebin: -

To reinstate the table, all we have to do is use the “Flashback Table” command.

 

SQL>flashback table test to before drop rename to test;

Flashback complete.



SQL> select * from test;

         A

----------

         1

         2



SQL> show recyclebin;

We can see the table test is back into the original state where it was before drop. But if the table has the indexes built on it, what happens to them? Lets see…

 

SQL> create table test(a number primary key);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> drop table test;

Table dropped.



SQL> select object_name,original_name,type from recyclebin;



OBJECT_NAME                    ORIGINAL_NAME

------------------------------ --------------------------------

TYPE

-------------------------

BIN$pjJQ48mbQl297TtOUMII6w==$0 SYS_C0016363

INDEX



BIN$a8rd/wnOQZexb+NOndiqjw==$0 TEST

TABLE



SQL> flashback table test to before drop rename to test;

Flashback complete.



SQL> select object_name,object_type from user_objects;



OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

PK_EMP                                		   INDEX

DEPT                             			TABLE

PK_DEPT                      			  INDEX

EMP                            			TABLE

BONUS                        			  TABLE

SALGRADE                  		   	  TABLE

TEST                          		    	 TABLE

BIN$pjJQ48mbQl297TtOUMII6w==$0 INDEX



8 rows selected.

The index still has the system-generated name assigned to it. If you want to get the original name back, you need to do it manually using

 

SQL> ALTER INDEX "BIN$pjJQ48mbQl297TtOUMII6w==$0" RENAME TO test_pk;



Index altered.



SQL> select object_name,object_type from user_objects;



OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

PK_EMP                         INDEX

DEPT                           TABLE

PK_DEPT                        INDEX

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

TEST                           TABLE

TEST_PK                        INDEX



8 rows selected.

Recyclebin also supports multiple versions of the same table so that we can get back to any older version: -

 

SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> show recyclebin;



ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------



TEST             BIN$JLFMRW9VTRCtxREKNeyDlg==$0 TABLE        2010-04-02:15:07:16



TEST             BIN$nydz2eBWTM+UJKbCyfKGzw==$0 TABLE        2010-04-02:15:07:16



TEST             BIN$sIjIxu4HRVu4gTp/KTKCkg==$0 TABLE        2010-04-02:15:07:16

Now we have all the three versions of test and we can restore to any table using our flashback command and using the recyclebin name.

 

SQL> Flashback table "BIN$sIjIxu4HRVu4gTp/KTKCkg==$0" to before drop rename to test3;



Flashback complete.



SQL> select * from test3;



      COL1

----------

         2