ORACLE Recycle Bin current status query collation _oracle

The Recycle Bin (recycle) is, in principle, a data dictionary table that places the database object information that the user deletes (drop). 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.

In the Oracle 10g database, a database object for a Recycle Bin (recycle Bin) is introduced.
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$$.

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

1.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:

Copy Code code as follows:

ALTER SYSTEM SET recyclebin = on;
ALTER session SET RecycleBin = on;
ALTER SYSTEM SET recyclebin = off;
ALTER session SET RecycleBin = off;

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

Copy Code code as follows:

SELECT * from RecycleBin;
SELECT * from User_recyclebin;
SELECT * from Dba_recyclebin;

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

Copy Code code as follows:

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.
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:

Copy Code code as follows:

PURGE TABLE <<Table_NAME>>;

B. Clear a specific index:

Copy Code code as follows:

PURGE INDEX <<Index_NAME>>;

C. Empty objects associated with this tablespace:

Copy Code code as follows:

PURGE tablespace<<table_name>>;

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

Copy Code code as follows:

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

E. Emptying the Recycle Bin:

Copy Code code as follows:

PURGE RecycleBin;

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

Copy Code code as follows:

DROP TABLE <<Table_Name>> PURGE;

Demo
1. Start the Recycle Bin function

Copy Code code as follows:

ALTER SYSTEM SET recyclebin = on;

2. Create a Demo_recyclebin table

Copy Code code as follows:

CREATE TABLE Demo_recyclebin (COL1 number);

3. Insert a piece of data into the Demo_recyclebin table

Copy Code code as follows:

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

4. Delete (drop) Demo_recyclebin table

Copy Code code as follows:

DROP TABLE Demo_recyclebin;

5. Query

Copy Code code as follows:

SELECT * from User_recyclebin;

Results:
6. Restore the Demo_recyclebin table from the Recycle Bin

Copy Code code as follows:

Flashback TABLE Demo_recyclebin to before DROP;

7. Post-Restore query

Copy Code code as follows:

SELECT * from Demo_recyclebin;

The query results are consistent with the prior deletion.
8. Delete the table and empty it from the Recycle Bin

Copy Code code as follows:

DROP TABLE Demo_recyclebin PURGE;