Oracle Recycle Bin (recyclebin) ____oracle

We are all familiar with the Recycle Bin in Windows, the file is deleted and placed in the Recycle Bin can be restored. The Oracle Recycle Bin works exactly the same way, there are some differences in the details of the implementation. In addition, only tables and related objects can be reclaimed in the Recycle Bin, including indexes, constraints, triggers, nested tables, large binary objects (LOB) segments, and LOB index segments. The function of the Recycle Bin starts from Orace 10g.

1. Recycle Bin startup and shutdown

The Recycle Bin is turned on by default. However, we can use the parameter recyclebin to close and open.

If only for each session you can pass the ALTER session set Recyclebin=off; and alter session set Recyclebin=on; To close and open

If it is for all sessions can be implemented by modifying the system parameter RecycleBin, but note that 10g and 11g are somewhat different, and their parameter types differ

10g can be used: Alter system set Recyclebin=off; and alter system set recyclebin=on; to close and open

11g can be used: Alter system set Recyclebin=off deferred; and alter system set Recyclebin=on deferred; to close and open the supplement: Let's have a quick look at the Oracle parameter types

Oracle parameter information can be viewed through view v$parameter, where one column issys_modifiable represents the parameter type, there are immediate,false,deferred three species, where false indicates static parameters, As long as the instance reboot will not take effect, the other two are dynamic parameters, immediate to change the parameters immediately after the effective, deferred said that after the change to wait for the next session to be effective, Sessions that have been connected when modified are not in effect. When modifying parameters with alter, if it is false or immediate generally can not write, if it is deferred 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 Recycle Bin Contents

SELECT * from User_recyclebin; or select * from RecycleBin; View the objects that are being drop by the current user (which has the name before the object and the name after the drop)

SELECT * from Dba_recyclebin; View all objects dropped by all users

in fact, when an object drops, and the Recycle Bin function is turned on. It's not really deleted, it actually just modifies the name, and we can find it with the select * from user_objects where type= ' TABLE '. Just its name is a little strange, for example Bin$qauuckgyd3tgqkjafaftag==$0, its naming convention is bin$unique_id$version where bin represents RecycleBin, Unique_ The ID is the unique flag for the object in the database, 26 characters in length, and version represents the edition number of the object. 3. Restore the contents of the Recycle Bin

If a table test is drop,drop after the object_name is bin$qauuckgyd3tgqkjafaftag==$0

You can use the Flashback table "Bin$qauuckgyd3tgqkjafaftag==$0" to before drop;(remember the name plus double quotes)

or flashback table test to before drop; but if you drop a table test, create a new table test, and then drop it. Then flashback table test to before drop can only be restored The last dropped table test. An error occurred while restoring the first dropped table because the table name cannot be duplicate. So it must be renamed Flashback table Test to before drop rename to Test2

4. Empty the Recycle bin

If you empty only one table at a time, you can use purge table test or purge table "bin$qauuckgyd3tgqkjafaftag==$0";

If you clear all at once, you can use purge RecycleBin or purge dba_recyclebin

When restoring, you can restore only one table at a time, and you cannot restore all tables at once, like emptying

5, the matters needing attention

If the table is under the system tablespace, you cannot use the Recycle Bin function, I wonder why there is such a limit.

If the table space is full, the system will automatically empty the contents of the Recycle Bin, first empty the first drop objects