The Recycle Bin (RecycleBin) is a data dictionary table in principle and stores the information of database objects deleted by users, next, let's take a look at all aspects of the ORACLE recycle bin. For more information, see
In principle, the Recycle Bin is a data dictionary table that stores information about database objects deleted by users, next, let's take a look at all aspects of the ORACLE recycle bin. For more information, see
In principle, the Recycle Bin is a data dictionary table that stores information about database objects deleted by users. Objects deleted by a user are not deleted by the database and still occupy space. Unless the user Purge manually or the database is cleared because the storage space is insufficient.
A Recycle Bin database object is introduced in Oracle 10 Gb database.
The recycle bin, as its name implies, stores deleted items. In principle, it is a data dictionary table that stores information about database objects deleted by users. Objects deleted by a user are not deleted by the database and still occupy space. Unless the user Purge manually or the database is cleared because the storage space is insufficient. The database has such a function, which can reduce a lot of unnecessary troubles.
If a table is deleted, objects associated with the table, such as indexes, constraints, and other dependent objects, are prefixed with bin $.
Start and close
You can use the following query statement to view the current status of the recycle bin:
1. SELECT Value from v $ parameter WHERE Name = 'recyclebin ';
If the returned value is "on", it indicates that the recycle bin is started, and "off" indicates that it is disabled.
Of course, you can start or stop each session and system in the recycle bin. The Code is as follows:
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;
Retrieve content from the recycle bin
You can use any of the following statements to obtain objects in the recycle bin:
The Code is as follows:
SELECT * from recyclebin;
SELECT * FROM USER_RECYCLEBIN;
SELECT * FROM DBA_RECYCLEBIN;
Restore
You can use the following syntax to restore the deleted object:
The Code is as follows:
Flashback table < > To before drop rename to < >;
Note: The RENAME here is an optional command to RENAME the deleted object.
Clear Recycle Bin
Here there are two types of clearing: First, you can clear with conditions, and second, clear all. Let's take a look at how to perform conditional clearing:
A. Clear a specific table:
The Code is as follows:
Purge table < >;
B. Clear a specific index:
The Code is as follows:
Purge index < >;
C. Clear the objects associated with the tablespace:
The Code is as follows:
Purge tablespace < >;
D. Clear the tablespace objects of a specific user:
The Code is as follows:
Purge tablespace < > USER < >;
E. Clear the recycle bin:
The Code is as follows:
Purge recyclebin;
F. When a table is deleted (drop), it is directly cleared from the recycle bin.
The Code is as follows:
Drop table < > PURGE;
Demo
1. Start the recycle bin Function
The Code is as follows:
Alter system set recyclebin = ON;
2. Create a DEMO_RECYCLEBIN table
The Code is as follows:
Create table DEMO_RECYCLEBIN (COL1 NUMBER );
3. Insert a data entry into the DEMO_RECYCLEBIN table
The Code is as follows:
Insert into DEMO_RECYCLEBIN (COL1) VALUES (1); COMMIT;
4. Delete (Drop) DEMO_RECYCLEBIN table
The Code is as follows:
Drop table DEMO_RECYCLEBIN;
5. Query
The Code is as follows:
SELECT * FROM USER_RECYCLEBIN;
Result:
6. Restore the DEMO_RECYCLEBIN table from the recycle bin.
The Code is as follows:
Flashback table DEMO_RECYCLEBIN to before drop;
7. query after restoration
The Code is as follows:
SELECT * FROM DEMO_RECYCLEBIN;
The query results are consistent with those before deletion.
8. Delete the table and clear it from the recycle bin.
The Code is as follows:
Drop table DEMO_RECYCLEBIN PURGE;
