Oracle Recycle Bin Use detailed

The Recycle Bin, as its name suggests, is the thing that stores the deleted items. The principle is a data dictionary table, put the user delete (drop) of the database object information. The object that the user is deleting is not deleted by the database and still takes up space. Unless the user is manually purge or the database is cleared because of insufficient storage space. The database has this function, can reduce a lot of unnecessary trouble.

If a table is deleted, objects that are associated with the table, such as indexes, constraints, and other dependent objects, are preceded by the prefix bin$$.

1. Startup and shutdown

You can use the following query to view the current status of the Recycle Bin:

SELECT Value from v$parameter WHERE Name = ' recyclebin ';

If the return value of "on" indicates that the Recycle Bin is started, "off" indicates that it is off.

Of course, you can start or turn off every session and system in the Recycle Bin, and the code is as follows:

ALTER SYSTEM SET recyclebin = on;

ALTER session SET RecycleBin = on;

ALTER SYSTEM SET recyclebin = off;

ALTER session SET RecycleBin = off;

2, get the contents of the Recycle Bin

You can use any of the following statements to get the objects in the Recycle Bin:

SELECT * from RecycleBin;

SELECT * from User_recyclebin;

SELECT * from Dba_recyclebin;

3, restore

You can use the following syntax to restore a deleted object:

This column more highlights: http://www.bianceng.cn/database/Oracle/

View Sourceprint?1 Flashback TABLE <<Dropped_Table_Name>> to before DROP RENAME to <<new_table_name >>;

Note: The rename here is to rename the deleted object, an optional command.

4, empty the Recycle Bin

The empty here contains two situations, first you can empty the condition, and the second is all empty. Let's take a look at the conditional emptying of what to do:

A. Clear a specific table:

PURGE TABLE <<Table_NAME>>;

B. Clear a specific index:

PURGE INDEX <<Index_NAME>>;

C. Empty objects associated with this tablespace:

PURGE tablespace<<table_name>>;

D. Empty a table space object for a particular user:

PURGE tablespace<<table_name>> USER <<User_Name>>;

E. Emptying the Recycle Bin:

PURGE RecycleBin;

F. When a table is deleted (drop), it is emptied directly from the Recycle Bin

DROP TABLE <<Table_Name>> PURGE;

Demo

1. Start the Recycle Bin function

ALTER SYSTEM SET recyclebin = on;

2. Create a Demo_recyclebin table

CREATE TABLE Demo_recyclebin (COL1 number);

3. Insert a piece of data into the Demo_recyclebin table

INSERT into Demo_recyclebin (COL1) VALUES (1); COMMIT;

4. Delete (drop) Demo_recyclebin table

DROP TABLE Demo_recyclebin;

5. Query

SELECT * from User_recyclebin;

Results:

6. Restore the Demo_recyclebin table from the Recycle Bin

Flashback TABLE Demo_recyclebin to before DROP;

7. Post-Restore query

SELECT * from Demo_recyclebin;

The query results are consistent with the prior deletion.

8. Delete the table and empty it from the Recycle Bin

DROP TABLE Demo_recyclebin PURGE;