Oracle DBA FAQ - Managing Oracle Database Tables

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5

(Continued from previous part...)

How To Turn On or Off Recycle Bin for the Instance?

You can turn on or off the recycle bin feature for an instance in the instance parameter file with "recyclebin=on/off". You can also turn on or off the recycle bin feature on the running instance with a SQL*Plus command, if you log in as SYSTEM. See the following example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> SHOW PARAMETERS RECYCLEBIN
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
recyclebin                           string      on

SQL> ALTER SYSTEM SET RECYCLEBIN = OFF;
System altered.

SQL> SHOW PARAMETERS RECYCLEBIN
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
recyclebin                           string      OFF

Warning: Turning off the recycle bin feature will give your users hard times on recovering dropped tables.

How To View the Dropped Tables in Your Recycle Bin?

You can look what's in your recycle bin through the predefined view called RECYCLEBIN. You can use the SELECT statement to list the dropped tables as shown in the following script:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_90 
  2  AS SELECT * FROM employees WHERE department_id=90;
Table created.

SQL> SELECT COUNT(*) FROM emp_dept_90;
  COUNT(*)
----------
         3

SQL> DROP TABLE emp_dept_90;
Table dropped.

SQL> COL original_name FORMAT A14
SQL> SELECT object_name, original_name, droptime
  2  FROM recyclebin;
OBJECT_NAME                    ORIGINAL_NAME DROPTIME
------------------------------ ------------- ---------------
BIN$uaSS/heeQuys53HgXRhEEQ==$0 EMP_DEPT_10   06-04-01:18:57:
BIN$gSt95r7ATKGUPuALIHy4dw==$0 EMP_DEPT_10   06-04-01:19:59:
BIN$bLukbcgSQ6mK1P2QVRf+fQ==$0 EMP_DEPT_90   06-04-01:20:47:

As you can use the EMP_DEPT_10 was dropped twice. If the same table was dropped multiple times, you need to restore by using the object name in the recycle bin with FLASHBACK statement.

Note that RECYCLEBIN is just an alias of USER_RECYCLEBIN.

How To Empty Your Recycle Bin?

If your recycle bin is full, or you just want to clean your recycle bin to get rid of all the dropped tables, you can empty it by using the PURGE statement in two formats:

PURGE RECYCLEBIN - Removes all dropped tables from your recycle bin.
PURGE TABLE table_name - Removes the specified table from your recycle bin.

Here is an example script on how to use the PURGE statement:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10 
  2  AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> DROP TABLE emp_dept_10;
Table dropped.

SQL> CREATE TABLE emp_dept_90 
  2  AS SELECT * FROM employees WHERE department_id=90;
Table created.

SQL> DROP TABLE emp_dept_90;
Table dropped.

SQL> SELECT COUNT(*) FROM recyclebin;
  COUNT(*)
----------
         5

SQL> PURGE TABLE emp_dept_90;
Table purged.

SQL> SELECT COUNT(*) FROM recyclebin;
  COUNT(*)
----------
         4

SQL> PURGE RECYCLEBIN;
Recyclebin purged.

SQL> SELECT COUNT(*) FROM recyclebin;
  COUNT(*)
----------
         0

(Continued on next part...)

Part: 1 2 3 4 5

More...