RecycleBin, oraclerecyclebin

RecycleBin, oraclerecyclebin
SQL> select * from v $ version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
SQL>! Cat/etc/issue
Enterprise Linux Server release 5.5 (Carthage)
Kernel \ r on an \ m
The recycle bin is a virtual container used to store deleted objects. In the recycle bin, deleted objects occupy the same space during creation.
You can disable the recycle bin function by using the recyclebin parameter:
Http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5160.htm#REFRN29036

Next we will not focus on Flashback.
SQL> show parameter recyclebin


NAME TYPE VALUE
-----------------------------------------------------------------------------
Recyclebin string on

SQL> alter system set recyclebin = off scope = spfile;


The system has been changed.


SQL> show parameter recyclebin


NAME TYPE VALUE
-----------------------------------------------------------------------------
Recyclebin string OFF

Create a table diy_ OS:
SQL> create table diy_ OS (id int );
The table has been created.
SQL> show user;
The USER is "HR"
SQL> drop table diy_ OS;
The table has been deleted.
View the deleted table diy_ OS:
SQL> select * from tab where rownum = 1;


TNAME TABTYPE CLUSTERID
-----------------------------------------------
BIN $ GCvSLxFD5lngUAB/AQA5vg ==$ 0 TABLE

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------
DIY_ OS BIN $ GCvSLxFD5lngUAB/AQA5vg ==$ 0 TABLE 2015-06-10: 23: 49: 32


SQL> select * from dba_recyclebin where original_name = 'diy _ OS ';


OWNER OBJECT_NAME ORIGINAL_NAME operation type TS_NAME createtime droptime dropscn PARTITION_NAME can related BASE_OBJECT PURGE_OBJECT SPACE
Certificate certificate --------- ---------------------------------------------------- ------------------- ---------- certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hr bin $ GCvSLxFD5lngUAB/AQA5vg ==$ 0 DIY_ OS DROP TABLE USERS 2015-06-10: 23: 49: 16 2015-06-10: 23: 49: 32 9610985 YES 78194 78194 0

Note: If you delete a sys user object, there is no record in the recycle bin, and oracle does not recommend creating an object in the sys User:
SQL> show user;
USER is "SYS"
SQL> create table kernel (id int );
The table has been created.
SQL> drop table kernel; the table has been deleted.

SQL> select * from dba_recyclebin where original_name = 'kernel ';
Unselected row

Purge recyclebin can be used to clear all objects in the recycle bin. purge user_recyclebin or purge dba_recyclebin can be used to delete objects by purge tablespace tablespace_name and purge table user. table_name.
Objects in the recycle bin
Note that when we use the drop tablespace... including contents command to delete a tablespace, all objects in the tablespace will be deleted, including the content in the recycle bin. When you use the drop user... cascade command to delete a user
All objects under will be deleted, including the content in the recycle bin.

SQL> purge table hr. diy_ OS;


The table has been cleared.


SQL> select * from dba_recyclebin where original_name = 'diy _ OS ';


Unselected row


See the execution plan below:
SQL> select * from dba_recyclebin;
Execution Plan
----------------------------------------------------------
Plan hash value: 1935272164


--------------------------------------------------------------------------------
--------------


| Id | Operation | Name | Rows | Bytes | Cost (% CP
U) | Time |


--------------------------------------------------------------------------------
--------------


| 0 | select statement | 2 | 336 | 12 (
9) | 00:00:01 |


| * 1 | FILTER |
|


| * 2 | hash join | 2 | 336 | 12 (
9) | 00:00:01 |


| 3 | nested loops |
|


| 4 | nested loops | 2 | 292 | 10 (
0) | 00:00:01 |


| 5 | nested loops | 2 | 202 | 6 (
0) | 00:00:01 |


| 6 | nested loops outer | 2 | 168 | 4 (
0) | 00:00:01 |


| 7 | table access full | RECYCLEBIN $ | 2 | 146 | 2 (
0) | 00:00:01 |


| 8 | table access cluster | TS $ | 1 | 11 | 1 (
0) | 00:00:01 |


| * 9 | index unique scan | I _TS # | 1 | 0 (
0) | 00:00:01 |


| 10 | table access cluster | USER $ | 1 | 17 | 1 (
0) | 00:00:01 |


| * 11 | index unique scan | I _USER # | 1 | 0 (
0) | 00:00:01 |


| * 12 | index range scan | I _OBJ1 | 1 | 1 (
0) | 00:00:01 |


| 13 | table access by index rowid | OBJ $ | 1 | 45 | 2 (
0) | 00:00:01 |


| 14 | index full scan | I _USER2 | 97 | 2134 | 1 (
0) | 00:00:01 |


| 15 | nested loops | 1 | 29 | 2 (
0) | 00:00:01 |


| * 16 | index full scan | I _USER2 | 1 | 20 | 1 (
0) | 00:00:01 |


| * 17 | index range scan | I _OBJ4 | 1 | 9 | 1 (
0) | 00:00:01 |


--------------------------------------------------------------------------------
--------------

The following is an excerpt from? /Rdbms/admin/dsqlddl. bsq
Create table recyclebin $
(
Obj # number not null,/* original object number */
Owner # number not null,/* owner user number */
Original_name varchar2 (32),/* Original Object Name */
Operation number not null,/* Operation carried out */
/* 0-> DROP */
/* 1-> TRUNCATE (not supported )*/
Type # number not null,/* object type (see KQD. H )*/
Ts # number,/* tablespace number */
File # number,/* segment header file number */
Block # number,/* segment header block number */
Droptime date,/* time when object was dropped */
Dropscn number,/* SCN of Tx which caused the drop */
Partition_name varchar2 (32),/* Name of the partition dropped */
/* NULL otherwise */
Flags number,/* flags for undrop processing */
Related number not null,/* obj one level up in heirarchy */
Bo number not null,/* base object */
Purgeobj number not null,/* obj to purge when purging this */
Base_ts # number,/* Base objects Tablespace number */
Base_owner # number,/* Base objects owner number */
Space number,/* number of blocks used by the object */
Con # number,/* con #, if index is due to constraint */
Spare1 number,
Spare2 number,
Spare3 number
)
/
Create index recyclebin $ _ obj on recyclebin $ (obj #)
/
Create index recyclebin $ _ ts on recyclebin $ (ts #)
/
Create index recyclebin $ _ owner on recyclebin $ (owner #)
/

The above is the creation and comment of the data dictionary recyclebin $, which will be further understood.