ORACLE recycle bin sorting

ORACLE recycle bin sorting
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. Www.2cto.com introduces a Recycle Bin database object in the Oracle 10g 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 $. You can use the following query statement to view the current status of the recycle bin: 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: alter system set recyclebin = ON; alter session set recyclebin = ON; alter system set recyclebin = OFF; alter session set recyclebin = OFF; you can use any of the following statements to retrieve the content in the recycle bin: SELECT * from recyclebin; SELECT * FROM USER_RECYCLEBIN; SELECT * FROM DBA_RECYCLEBIN; you can use the following syntax TO restore the deleted object: 1 flashback table <Dropped_Table_Name> to before drop rename to <New_T Able_Name >>; note: the RENAME here is an optional command to RENAME the deleted object. Clearing the recycle bin contains two conditions: first, you can clear the recycle bin with conditions, and second, clear all. Let's first look at how to do conditional clearing:. clear a specific TABLE: purge table <Table_NAME>; B. clear a specific INDEX: purge index <Index_NAME>; c. clear the objects associated with the TABLESPACE: purge tablespace <Table_NAME>; d. clear the TABLESPACE objects of a specific USER: purge tablespace <Table_NAME> USER <User_Name>; e. clear the recycle bin: purge recyclebin; f. when a TABLE is deleted (drop), the drop table <Table_Name> PURGE; Demo1. enable the recycle bin function alter system set recyclebin = ON; 2. create table DEMO_RECYCLEBIN (COL1 NUMBER); 3. INSERT a data insert into DEMO_RECYCLEBIN (COL1) VALUES (1); COMMIT; 4. delete (Drop) DEMO_RECYCLEBIN table drop table DEMO_RECYCLEBIN; 5. query SELECT * FROM USER_RECYCLEBIN; Result: 6. restore the DEMO_RECYCLEBIN table flashback table DEMO_RECYCLEBIN to before drop from the recycle bin; 7. query SELECT * FROM DEMO_RECYCLEBIN after restoration; the query results are consistent with those before deletion. 8. Delete the TABLE and clear it from the recycle bin drop table DEMO_RECYCLEBIN PURGE;