Oracle Recycle Bin (Recycle Bin)

We are all familiar with the recycle bin in windows. After a file is deleted and put into the recycle bin, it can be restored. The Oracle recycle bin works exactly the same, but there are some differences in implementation details.. In addition, the recycle bin can only recycle tables and related objects, including indexes, constraints, triggers, nested tables, large binary object (LOB) segments, and LOB index segments. the recycle bin function is available from Orace 10g.

1. Start and close the recycle bin

The recycle bin is enabled by default. However, you can disable and enable it by using the recyclebin parameter.

For each session, you can use alter session set recyclebin = off; and alter session set recyclebin = on; to close and enable

For all sessions, you can modify the system parameter recyclebin. HoweverNote that 10g and 11G are a little different, and their parameter types are different.

10 Gb can be used: alter system set recyclebin = off; and alter system set recyclebin = on; to close and enable

11g can be used: alter system set recyclebin = off deferred; and alter system set recyclebin = on deferred; to close and enable

Supplement: Let's take a brief look at the Oracle parameter types.

The Oracle parameter information can be viewed in the view v $ parameter. An issys_modifiable column indicates the parameter type,Three types are available: immediate, false, and deferred."False" indicates a static parameter, which takes effect only when the instance is restarted. The other two parameters are dynamic parameters. "immediate" indicates that the parameter takes effect immediately after modification, deferred indicates that the modified session will not take effect until the next session takes effect. when you use alter to modify a parameter, if it is false or immediate, it can be left blank. If it is deferred, you need to write

10g: select ISSYS_MODIFIABLE from v $ parameter where name = 'recyclebin'; the result is immediate.

11g: select ISSYS_MODIFIABLE from v $ parameter where name = 'recyclebin'; the result is deferred.

2. view the content of the recycle bin

Select * from user_recyclebin; Or select * from recyclebin; view the object to be dropped by the current user (including the object's previous name and the name to be dropped)

Select * from dba_recyclebin;View all user drop objects

In fact, after an object is dropped, the recycle bin function is enabled. it is not actually deleted. In fact, we only modified the name. We can still find it using select * from user_objects where type = 'table. its name is a bit strange, for example, BIN $ qAUuckGyd3TgQKjAFAFTAg ==$ 0. Its naming convention is BIN $ unique_id $ version, where BIN represents RecycleBin, unique_id is the unique identifier of the object in the database. It is 26 characters in length and version indicates the version number of the object.

3. Restore the content of the recycle bin

If a table test is dropped, the object_name after drop is BIN $ qAUuckGyd3TgQKjAFAFTAg ==$ 0

You can use flashback table "BIN $ qAUuckGyd3TgQKjAFAFTAg = $0" to before drop; (remember to add the name with double quotation marks)

OrFlashback table test to before drop; To restore. however, if you drop a table test, create a new table test, and then drop. therefore, flashback table test to before drop can only restore the test table that is finally dropped. an error occurs when the first drop table is restored because the table name cannot be duplicated. therefore, you must rename the flashback table test to before drop rename to test2.

4. Clear the recycle bin

If only one table is cleared at a time, you can use PURGE table test or PURGE table "BIN $ qAUuckGyd3TgQKjAFAFTAg ==$ 0 ";

If all objects can be cleared at a time, use PURGE recyclebin or PURGE dba_recyclebin.

Only one table can be restored at a time. All tables cannot be restored at a time as they are cleared.

5. Notes

If the table is under the system tablespace, you cannot use the recycle bin function.

If the tablespace is full, the system automatically clears part of the content in the recycle bin. First, the earliest drop object is cleared.