Shulou(Shulou.com)05/31 Report--

This article mainly introduces the concept and function of the Oracle Recycle Bin. In daily operation, I believe many people have doubts about the concept and function of the Oracle Recycle Bin. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about the concept and function of the Oracle Recycle Bin. Next, please follow the editor to study!

Catalogue

I. the concept of Recycle Bin

II. Recycle Bin function

Manage the Recycle Bin

IV. Examples

1. Delete the table with the same name, and then flash the table.

2. Flashback Drop can only be used for non-system table spaces and locally managed table spaces

3. Understand the process of renaming

4. The influence of deleted table and flashback deleted table on indexes and constraints

5. When the table space is insufficient, the table deletion cannot be flashed.

11g official documentation explanation of RecycleBin

I. the concept of Recycle Bin

Starting with ORACLE 10g, the concept of Recycle Bin (Recycle Bin) was introduced. Its full name is Tablespace Recycle Bin. The Recycle Bin is actually a logical container (logical area), and the principle is somewhat similar to the Recycle Bin of the WINDOW system. It is based on the existing allocated space in the tablespace, rather than physically delimiting a fixed area from the tablespace to be used as a recycle bin. This means that the recycle bin and objects in the tablespace share a storage area, and the system does not reserve space for the recycle bin. Therefore, when the table is DROP, if there is enough free space and the Recycle Bin is not cleaned, the objects dropped by DROP will always be in the Recycle Bin, but if the available space is tight, the database will overwrite the objects in the Recycle Bin according to the first-in-first-out order. Therefore, the recycle bin mechanism is not a 100% insurance mechanism. In addition, in principle, it is a data dictionary table, which places the database object information dropped by the user Drop. The objects that users perform Drop operations are not really deleted by the database and will still take up space. Unless it is due to manual Purge by the user or cleared by the database because there is not enough storage space. With such a function, the database can reduce a lot of unnecessary trouble. When users, developers, or even DBA mistakenly delete tables, then we do not have to restore the entire database or tablespace, but directly use the FLASHBACK (FLASHBACK) function of ORACLE 10g to restore deleted tables. In this way, we can avoid a lot of manual misoperation. This is a very useful feature for DBA.

Flashback Drop is based on Tablespace RecycleBin to achieve recovery. It only supports flashback of objects associated with table, such as tables, indexes, constraints, triggers, etc. If it is a function or stored procedure, you need to use Flashback Query to implement it.

II. Recycle Bin function

The main benefit of the Recycle Bin feature is that when a table is mistakenly deleted, there is a recovery mechanism that does not have to be restored through the database. Avoid a large number of manual misoperation. And complex operations such as database restore. Make the management and maintenance of the database more simple and convenient. If it is a SQL SERVER database, you must restore the entire database to find the table that was dropped by DROP. It can be seen that the Recycle Bin function is indeed a groundbreaking feature.

Manage the Recycle Bin

1. Open and close the Recycle Bin

First of all, you can check whether the database has enabled the Recycle Bin mechanism with the command. VALUE= ON below indicates that the Recycle Bin mechanism is enabled. OFF indicates that the Recycle Bin mechanism is turned off.

SYS@seiang11g > show parameter recyclebin

NAME TYPE VALUE

-

Recyclebin string on

Or

SYS@seiang11g > select name,value from v$parameter where name='recyclebin'

NAME VALUE

Recyclebin on

You can enable or disable the Recycle Bin feature by setting the initialization parameter recyclebin. Of course, you can also use the command to close the Recycle Bin.

SYS@seiang11g > alter system set recyclebin=off scope=spfile

System altered.

SYS@seiang11g > alter session set recyclebin=off

Session altered.

SYS@seiang11g > show parameter recyclebin

NAME TYPE VALUE

-

Recyclebin string OFF

You can open the Recycle Bin with the command

SYS@seiang11g > alter system set recyclebin=on scope=spfile

System altered.

SYS@seiang11g > alter session set recyclebin=on

Session altered.

SYS@seiang11g > show parameter recyclebin

NAME TYPE VALUE

-

Recyclebin string ON

View Recycle Bin object

Let's take a look at an example, as shown below. If you accidentally manipulate the table wjq1 by DROP, then view the table objects that are DROP in the Recycle Bin.

SYS@seiang11g > select owner,table_name,tablespace_name from dba_tables where owner='SEIANG'

OWNER TABLE_NAME TABLESPACE_NAME

-

SEIANG WJQ1 SEIANG

SYS@seiang11g > drop table seiang.wjq1

Table dropped.

SEIANG@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

WJQ1 BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18 purl 14 purl 35 purl 07

RECYCLEBIN is synonymous with USER_RECYCLEBIN.

SEIANG@seiang11g > select object_name,original_name,operation,type,droptime,ts_name from recyclebin

OBJECT_NAME ORIGINAL_N OPERATION TYPE DROPTIME TS_NAME

BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 WJQ1 DROP TABLE 2017-08-18 14 15 15 15 07 SEIANG

View the Recycle Bin object for the current user of the database

SQL > SELECT * FROM USER_RECYCLEBIN

-- View all objects in the database recycle bin

SQL > SELECT * FROM DBA_RECYCLEBIN

IV. Examples

1. Delete the table with the same name, and then flash the table.

In order to avoid duplicate names of deleted tables and similar objects, after the deleted tables and dependent objects are placed in the Recycle Bin, the ORACLE database will rename the deleted object names, such as table wjq1

SEIANG@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

WJQ1 BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18 purl 14 purl 35 purl 07

We created the table wjq1, and then deleted the table wjq1, as shown below, although the ORIGINAL_NAME is the same, but the RECYCLEBIN NAME is different.

SEIANG@seiang11g > create table wjq1 (name varchar2 (20), address varchar2 (20)) tablespace good

Table created.

SEIANG@seiang11g > insert into wjq1 values ('wjq','beijing')

1 row created.

SEIANG@seiang11g > insert into wjq1 values ('seiang','lanzhou')

1 row created.

SEIANG@seiang11g > commit

Commit complete.

SEIANG@seiang11g > drop table wjq1

Table dropped.

SEIANG@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

WJQ1 BIN$VwJ/MWH8dIfgUy4BAQq2eQ==$0 TABLE 2017-08-18 purl 14 purl 47 purl 04

WJQ1 BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18 purl 14 purl 35 purl 07

SEIANG@seiang11g > select * from "BIN$VwJ/MWH8dIfgUy4BAQq2eQ==$0"

NAME ADDRESS

--

Wjq beijing

Seiang lanzhou

SEIANG@seiang11g > select * from "BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0"

ID NAME

1 wjq1

2 wjq2

3 wjq3

Restore Recycle Bin object

Restoring objects such as tables and indexes that have been deleted by the Recycle Bin is achieved through Flashback Drop. As shown below.

SEIANG@seiang11g > flashback table wjq1 to before drop

Flashback complete.

SEIANG@seiang11g > select * from wjq1

NAME ADDRESS

--

Wjq beijing

Seiang lanzhou

SEIANG@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

WJQ1 BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18 purl 14 purl 35 purl 07

As shown above, if two tables with the same name wjq1 are deleted, flashback of DROP table wjq1 is essentially flashback of the last deleted table (LIFO principle). If the flashback operation continues at this time, an ORA-38312 error will be reported.

SEIANG@seiang11g > flashback table wjq1 to before drop

Flashback table wjq1 to before drop

*

ERROR at line 1:

ORA-38312: original name is used by an existing object

At this point, you can rename the table name during flashback to solve the problem.

SEIANG@seiang11g > flashback table wjq1 to before drop rename to wjq1_test

Flashback complete.

SEIANG@seiang11g > select * from wjq1_test

ID NAME

1 wjq1

2 wjq2

3 wjq3

When you look at the contents of the Recycle Bin again, you find that it is gone.

In addition, if there are two tables wjq1 dropped by DROP in the Recycle Bin, what if you want to flash back the first deleted table?

SEIANG@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

WJQ1 BIN$VwJ/MWH+dIfgUy4BAQq2eQ==$0 TABLE 2017-08-18 purl 14 purl 59 purl 11

WJQ1 BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0 TABLE 2017-08-18 purl 14 purl 58 purl 05

In fact, this is also very easy to deal with, directly specify RECYCLEBIN NAME for flashback.

SEIANG@seiang11g > flashback table "BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0" to before drop

Flashback complete.

SEIANG@seiang11g > select * from wjq1

NAME ADDRESS

--

Wjq beijing

Seiang lanzhou

Empty the Recycle Bin

After the database object is deleted, the database will rename it to the object at the beginning of BIN$, and you can view its corresponding original object name through ORIGINAL_NAME. Remember, putting the table in the Recycle Bin does not free up space in the original tablespace. If you want to delete the table completely without putting it in the Recycle Bin, you can permanently delete the table using the following command. Of course, after doing this, you can't flash back to the table by using the flashback feature.

DROP TABLE [TABLE_NAME] PURGE

If the deleted tables in the database are placed in the Recycle Bin and the occupied space is not released, will the deleted tables encroach on the storage space when there is insufficient free space?

The answer is simple: when the tablespace is so full of Recycle Bin data that the data file must be expanded to accommodate more data, it can be said that the tablespace is under "space pressure". At this point, the object is automatically cleared from the Recycle Bin on a first-in-first-out basis. Related objects, such as indexes, are deleted before the table is deleted.

Similarly, space pressure can be caused by user limits defined by a particular tablespace. The tablespace may have enough free space, but the user may have used up the portion of the tablespace allocated to it. In this case, Oracle automatically clears the objects in the tablespace that belong to the user.

In addition, there are several ways to manually control the Recycle Bin. If you need to clear a specific table named TEST from the Recycle Bin after deleting it, you can execute the

PURGE TABLE [TABLE_NAME]

Or use the name in its Recycle Bin:

PURGE TABLE "BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0"

This command saves space by deleting table TEST and all related objects, such as indexes, constraints, and so on, from the Recycle Bin. However, if you want to permanently delete the index from the Recycle Bin, you can use the following command to do the job:

PURGE INDEX [INDEX_NAME]

This command will only delete the index and leave a copy of the table in the Recycle Bin. Sometimes purging at a higher level may be useful. For example, you might want to clear all objects in the Recycle Bin in tablespace USERS. You can perform:

PURGE TABLESPACE USERS

You may want to empty the Recycle Bin only for specific users in that tablespace. This approach can be useful in a data warehouse type environment where users create and delete many temporary tables. You can change the above command to limit the clearance to specific users:

PURGE TABLESPACE USERS USER SCOTT

To free up the space occupied by the entire Recycle Bin, you need to empty the Recycle Bin using the following command:

PURGE RECYCLEBIN

Remember that PURGE RECYCLEBIN only clears the objects in the current user's Recycle Bin, and the objects under DBA_RECYCLEBIN are not deleted. If you want to clear the objects in the current database Recycle Bin, you must use the following command (DBA permission)

PURGE DBA_RECYCLEBIN

2. Flashback Drop can only be used for non-system table spaces and locally managed table spaces

-- the sys_table table created belongs to the non-system tablespace SEIANG

SYS@seiang11g > create table sys_table (id number,idcard number) tablespace seiang

Table created.

SYS@seiang11g > insert into sys_table values (1mai 123456)

1 row created.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > select owner,table_name,tablespace_name from dba_tables where table_name='SYS_TABLE'

OWNER TABLE_NAME TABLESPACE_NAME

-

SYS SYS_TABLE SEIANG

SYS@seiang11g > drop table sys_table

Table dropped.

SYS@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

SYS_TABLE BIN$VwJUfr+DbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18 purge 15 purl 08purl 17

The created sys_table1 table belongs to the system tablespace SYSTEM

SYS@seiang11g > create table sys_table1 (id number,name varchar2 (20))

Table created.

SYS@seiang11g > insert into sys_table1 values (1)

1 row created.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > select owner,table_name,tablespace_name from dba_tables where table_name='SYS_TABLE1'

OWNER TABLE_NAME TABLESPACE_NAME

-

SYS SYS_TABLE1 SYSTEM

SYS@seiang11g > drop table sys_table1

Table dropped.

SYS@seiang11g > show recyclebin

As can be seen from the above example, in the system tablespace, the table object is actually deleted from the system after it is deleted, rather than stored in the Recycle Bin.

3. The process of renaming

-- create a tab_ emptable based on emp table

SCOTT@seiang11g > create table tab_emp as select * from emp

Table created.

-- adding a primary key constraint will automatically generate a primary key index

SCOTT@seiang11g > alter table tab_emp add constraint pk_empno primary key (empno)

Table altered.

-- adding a unique key constraint will automatically generate a unique index

SCOTT@seiang11g > alter table tab_emp add constraint uk_ename unique (ename)

Table altered.

-- add check constraint

SCOTT@seiang11g > alter table tab_emp add constraint ck_sal check (sal > 0)

Table altered.

-- add a non-empty constraint

SCOTT@seiang11g > alter table tab_emp modify job constraint nn_job not null

Table altered.

-- add foreign key constraint

SCOTT@seiang11g > alter table tab_emp add constraint fk_dept foreign key (deptno) references dept (deptno) on delete cascade

Table altered.

-- View all constraints and indexes on the tab_ emptable

SCOTT@seiang11g > select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints

2 where table_name='TAB_EMPLOYEE'

3 union all

4 select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes

5 where table_name='TAB_EMPLOYEE'

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME

SCOTT PK_EMPNO P TAB_EMPLOYEE

SCOTT UK_ENAME U TAB_EMPLOYEE

SCOTT CK_SAL C TAB_EMPLOYEE

SCOTT NN_JOB C TAB_EMPLOYEE

SCOTT FK_DEPT R TAB_EMPLOYEE

SCOTT UK_ENAME NORMAL TAB_EMPLOYEE

SCOTT PK_EMPNO NORMAL TAB_EMPLOYEE

-- View the id of the file where the tab_ emptable is located, the starting id of the block, and the size

SYS@seiang11g > select file_id,block_id,bytes from dba_extents where segment_name='TAB_EMP'

FILE_ID BLOCK_ID BYTES

4 528 65536

-- View the id of the tab_ emptable object

SYS@seiang11g > select object_name,object_id from dba_objects where object_name='TAB_EMP'

OBJECT_NAME OBJECT_ID

TAB_EMP 89445

Rename the tab_ emptable to tab_employee

SCOTT@seiang11g > alter table tab_emp rename to tab_employee

Table altered.

-- check the id of the file where the renamed table tab_employee is located, the starting id of the block, and the size, and find that there is no change.

SYS@seiang11g > select file_id,block_id,bytes from dba_extents where segment_name='TAB_EMPLOYEE'

FILE_ID BLOCK_ID BYTES

4 528 65536

Look at the id of the renamed table tab_employee object and find that no change has taken place

SYS@seiang11g > select object_name,object_id from dba_objects where object_name='TAB_EMPLOYEE'

OBJECT_NAME OBJECT_ID

TAB_EMPLOYEE 89445

Look at all the constraints and indexes on the renamed table tab_emp and find that no changes have taken place

SCOTT@seiang11g > select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints

2 where table_name='TAB_EMPLOYEE'

3 union all

4 select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes

5 where table_name='TAB_EMPLOYEE'

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME

SCOTT PK_EMPNO P TAB_EMPLOYEE

SCOTT UK_ENAME U TAB_EMPLOYEE

SCOTT CK_SAL C TAB_EMPLOYEE

SCOTT NN_JOB C TAB_EMPLOYEE

SCOTT FK_DEPT R TAB_EMPLOYEE

SCOTT UK_ENAME NORMAL TAB_EMPLOYEE

SCOTT PK_EMPNO NORMAL TAB_EMPLOYEE

From the above demonstration, we can see that the renaming of the table only changes the table name, but there is no substantial change in the ID of the table object, the location of the table, the start of the block, the size, and so on.

4. The influence of deleted table and flashback deleted table on indexes and constraints

Delete the tab_employee table and view the Recycle Bin

SCOTT@seiang11g > drop table tab_employee

Table dropped.

SCOTT@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP T

-

TAB_EMPLOYEE BIN$VwRUrQdoe8vgUy4BAQoEBw==$0 TABLE 2017-08-18 purl 16 purl 58 purl 20

SCOTT@seiang11g > select object_name,original_name,can_undrop,base_object from user_recyclebin

OBJECT_NAME ORIGINAL_NAME CAN BASE_OBJECT

BIN$VwRUrQdme8vgUy4BAQoEBw==$0 PK_EMPNO NO 89445

BIN$VwRUrQdne8vgUy4BAQoEBw==$0 UK_ENAME NO 89445

BIN$VwRUrQdoe8vgUy4BAQoEBw==$0 TAB_EMPLOYEE YES 89445

-- tab_employee table cannot be queried

SCOTT@seiang11g > select count (*) from tab_employee

Select count (*) from tab_employee

*

ERROR at line 1:

ORA-00942: table or view does not exist

-- after deleting the tab_employee table, you can view it through the name of the Recycle Bin

SCOTT@seiang11g > select count (*) from "BIN$VwRUrQdoe8vgUy4BAQoEBw==$0"

COUNT (*)

-

fourteen

-- View all constraints and indexes on the tab_ employees table

SCOTT@seiang11g > select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints

2 where table_name='TAB_EMPLOYEE'

No rows selected

SCOTT@seiang11g >

SCOTT@seiang11g > select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes

2 where table_name='TAB_EMPLOYEE'

No rows selected

-- flashback deleted tab_ employees table from the Recycle Bin

SCOTT@seiang11g > flashback table tab_employee to before drop

Flashback complete.

-- the table exists after flashback and can be accessed

SCOTT@seiang11g > select count (*) from tab_employee

COUNT (*)

-

fourteen

-- after deletion, check the name of the constraint and index, which is still the name of the Recycle Bin. BIN$, also found that the foreign key constraint disappeared.

SCOTT@seiang11g > select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints

2 where table_name='TAB_EMPLOYEE'

3 union all

4 select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes

5 where table_name='TAB_EMPLOYEE'

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME

SCOTT BIN$VwRUrQdie8vgUy4BAQoEBw==$0 P TAB_EMPLOYEE

SCOTT BIN$VwRUrQdje8vgUy4BAQoEBw==$0 U TAB_EMPLOYEE

SCOTT BIN$VwRUrQdke8vgUy4BAQoEBw==$0 C TAB_EMPLOYEE

SCOTT BIN$VwRUrQdle8vgUy4BAQoEBw==$0 C TAB_EMPLOYEE

SCOTT BIN$VwRUrQdne8vgUy4BAQoEBw==$0 NORMAL TAB_EMPLOYEE

SCOTT BIN$VwRUrQdme8vgUy4BAQoEBw==$0 NORMAL TAB_EMPLOYEE

From the above query, we can see that after the flashback, the name of the index and constraint still uses the name that begins with BIN$ and is generated by the system, which can be changed back, but the foreign key constraint no longer exists.

Attempt to DML the table

-- insert data and find that it can be inserted successfully. The foreign key constraint of deptno column has been deleted, so deptno is successfully inserted at number 70.

SCOTT@seiang11g > insert into tab_employee (empno,ename,job,sal,deptno) select 666, "seiangjia", "DBA,"5000,"

1 row created.

Change the index and constraint at the beginning of BIN$ back to the original name

SCOTT@seiang11g > alter index "BIN$VwRUrQdme8vgUy4BAQoEBw==$0" rename to pk_empno

Index altered.

SCOTT@seiang11g > alter index "BIN$VwRUrQdne8vgUy4BAQoEBw==$0" rename to UK_ENAME

Index altered.

SCOTT@seiang11g > alter table tab_employee rename constraint "BIN$VwRUrQdle8vgUy4BAQoEBw==$0" to NN_JOB

Table altered.

SCOTT@seiang11g > alter table tab_employee rename constraint "BIN$VwRUrQdke8vgUy4BAQoEBw==$0" to ck_sal

Table altered.

SCOTT@seiang11g > alter table tab_employee rename constraint "BIN$VwRUrQdje8vgUy4BAQoEBw==$0" to uk_ename

Table altered.

SCOTT@seiang11g > alter table tab_employee rename constraint "BIN$VwRUrQdie8vgUy4BAQoEBw==$0" to pk_empno

Table altered.

5. When the table space is insufficient, the table deletion cannot be flashed.

-- create an tab_test tablespace with a size of 1m and cannot be automatically extended

SYS@seiang11g > create tablespace tab_test datafile'/ u01 size size

Tablespace created.

-- check that tab_test tablespaces are not automatically extended

SYS@seiang11g > select t.tablespaceplayname. Filewriting name, authoring. Autoextensible.

2 from dba_tablespaces t,dba_data_files d

3 where t.tablespace_name=d.tablespace_name

TABLESPACE_NAME FILE_NAME AUT

USERS / u01/app/oracle/oradata/OraDB11g/users01.dbf YES

UNDOTBS1 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf YES

SYSAUX / u01/app/oracle/oradata/OraDB11g/sysaux01.dbf YES

SYSTEM / u01/app/oracle/oradata/OraDB11g/system01.dbf YES

EXAMPLE / u01/app/oracle/oradata/OraDB11g/example01.dbf YES

SEIANG / u01/app/oracle/oradata/OraDB11g/seiang01.dbf NO

GOOD / u01/app/oracle/oradata/OraDB11g/good01.dbf NO

TAB_TEST / u01/app/oracle/oradata/OraDB11g/tab_test01.dbf NO

-- View the free space of the tab_test tablespace

SYS@seiang11g > select tablespace_name,sum (bytes/1024/1024) | |'M'

2 from dba_free_space where tablespace_name='TAB_TEST' group by tablespace_name

TABLESPACE_NAME SUM (BYTES/1024/1024) | |'M'

TAB_TEST. 9375 M

-- create a test table on the tab_test tablespace that belongs to the scott user, create and insert data at the same time

SYS@seiang11g > create table scott.test tablespace tab_test as select * from dba_objects where rownumselect tablespace_name,sum (bytes/1024/1024) | |'M'

2 from dba_free_space where tablespace_name='TAB_TEST' group by tablespace_name

TABLESPACE_NAME SUM (BYTES/1024/1024) | |'M'

TAB_TEST. 25 M

-- delete the test table, but do not purge

SYS@seiang11g > drop table scott.test

Table dropped.

-- after deleting the test table, check the free size of the tab_test tablespace, which is 1m, but not the real 1m. When the tablespace is needed, the oldest objects in the Recycle Bin will be automatically cleared to meet the current space requirements.

SYS@seiang11g > select tablespace_name,sum (bytes/1024/1024) | |'M'

2 from dba_free_space where tablespace_name='TAB_TEST' group by tablespace_name

TABLESPACE_NAME SUM (BYTES/1024/1024) | |'M'

TAB_TEST. 9375 M

-- View the information of the Recycle Bin. The deleted test table object is in the Recycle Bin.

SCOTT@seiang11g > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

TEST BIN$VwJUfr+EbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18 purge 17 purl 35 purl 33

Then, create the test2 table, which belongs to the tab_test tablespace

SYS@seiang11g > create table test2 tablespace tab_test as select * from dba_objects where rownumshow recyclebin

SCOTT@seiang11g > select object_name,original_name,can_undrop,base_object from user_recyclebin

No rows selected

-- test cannot be flashed back at this time

SCOTT@seiang11g > flashback table test to before drop

Flashback table test to before drop

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

Summary:

1. The deletion of the table is mapped to rename the table and then place it in the Recycle Bin.

2. The index, trigger and authorization flashback of the table will not be affected. Index, the trigger name can be changed back to the original name as needed.

3. For constraints, if it is a foreign key constraint, the table cannot be restored after deletion, and the rest of the constraints will not be affected.

4, when deleting a table, the materialized view that depends on the table will also be deleted, but because the materialized view will not be put into recycle bin, so when you execute flashback drop, you can not restore the materialized view that depends on it. Manual reconstruction by DBA is required.

5. If you want to query objects in the recycling bin, it is recommended that you enclose the object names in double quotation marks.

6. For objects in the Recycle Bin (Recycle Bin), only queries are supported. No other DML, DDL, and other operations are supported.

7. The essence of flashback is not to undo the committed transaction, but to construct another transaction affected by the retrogression of the original transaction.

8. For deleted tables, flashbacks will fail if new objects are reused due to the pressure of insufficient space in the table space.

9. When the table space is insufficient, the system will automatically clear the oldest objects in the Recycle Bin to meet the current needs, that is, the principle of FIFO will be adopted.

10. Common methods of flashback table

Flashback table tbname to before drop

Flashback table [tbname] to before drop rename to [newtbname]

The second statement for the deleted table name has been reused, so it must be renamed to the new table name before flashback, and the schema will not change.

11. If there are two same original table names in the Recycle Bin, the most recent version is always flashed back. If a specific table is flashed back, you need to specify the name of the table in the Recycle Bin. Such as

Flashback table "BIN$k1zC3yEiwZvgQAB/AQBRVw==$0" to before drop

12. Lashback drop cannot flash back the table truncated by the truncate command, but can only restore the table after drop

13. Flashback drop cannot flash back the operation of drop user scott cascade deletion scheme. This can only be done with flashback database.

14. Tables stored in system tablespaces cannot be flashback drop enabled, and these tables will be deleted immediately

The following is the description of recyclebin in 11g official documentation:

*

Using Flashback Drop and Managing the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered ata later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table. Before discussing the use of the FLASHBACK TABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.

This section contains the following topics:

What Is the Recycle Bin?

Viewing and Querying Objects in the Recycle Bin

Purging Objects in the Recycle Bin

Restoring Tables from the Recycle Bin

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

The Recycle Bin is actually a data dictionary table that contains information about deleting objects. Deleted tables and any of their associated objects (such as indexes, constraints, nested tables, etc.) are not deleted and still take up space. They continue to calculate user space quotas until they are specifically cleared from the Recycle Bin, or the database must clear them because of tablespace constraints.

Each user can be thought of as having his own recycle bin, because, unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

Each user can be considered to have his own Recycle Bin, because unless the user has SYSDBA privileges, the only objects that the user accesses in the Recycle Bin are the objects that the user owns.

SELECT * FROM RECYCLEBIN

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

When you delete a tablespace that contains its contents, the objects in the tablespace are not placed in the Recycle Bin, and the database clears any entries for objects in the tablespace in the Recycle Bin. When you delete a tablespace (excluding content), the database also clears any Recycle Bin entries for objects in the tablespace, otherwise the tablespace is empty. Similarly:

When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.

When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.

When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

When you delete a user, any objects belonging to the user are not placed in the Recycle Bin and any objects in the Recycle Bin are cleaned.

When you delete a cluster, its member table is not placed in the Recycle Bin and any previous member tables in the Recycle Bin are cleared.

When you delete a type, any dependent objects (such as subtypes) are not placed in the Recycle Bin and any previous dependent objects in the Recycle Bin are cleared.

Object Naming in the Recycle Bin

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

When the deleted table is moved to the Recycle Bin, the table and its associated objects are given the system-generated name. This is necessary to avoid name conflicts that may occur when multiple tables have the same name. This can happen in the following situations:

A user drops a table, re-creates it with the same name, then drops it again.

Two users have tables with the same name, and both users drop their tables.

The user deletes the table, recreates a table, and then deletes it again.

Two users have tables with the same name, and both users delete their tables.

The renaming convention is as follows:

BIN$unique_id$version

Where:

Unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases

Version is a version number assigned by the database

Unique_id is a globally unique identifier of 26 characters for this object, which makes the Recycle Bin name unique in all databases

Version is the version number assigned by the database

Enabling and Disabling the Recycle Bin

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

When the Recycle Bin is enabled, discarded tables and their related objects are placed in the Recycle Bin. When the Recycle Bin is disabled, discarded tables and their related objects are not placed in the Recycle Bin; they have just been deleted and you must use other methods to restore them (such as restoring from a backup).

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

Disabling the Recycle Bin does not clear or affect objects that are already in the Recycle Bin. The Recycle Bin is enabled by default.

You enable and disable the recyclebin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.

Enable and disable the Recycle Bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so you need to restart the database when you use the ALTER SYSTEM statement to change the database.

To disable the recycle bin:

Issue one of the following statements:

2. ALTER SESSION SET recyclebin = OFF

3.

4. ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE

If you used ALTER SYSTEM, restart the database.

To enable the recycle bin:

Issue one of the following statements:

2. ALTER SESSION SET recyclebin = ON

3.

4. ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE

If you used ALTER SYSTEM, restart the database.

Viewing and Querying Objects in the Recycle Bin

Oracle Database provides two views for obtaining information about objects in the recycle bin:

View

Description

USER_RECYCLEBIN

This view can be used by users to see their own dropped objects in the recycle bin. It has a synonymRECYCLEBIN, for ease of use.

Users can use this view to view their deleted objects in the Recycle Bin. It has the synonym RECYCLEBIN and is easy to use.

DBA_RECYCLEBIN

This view gives administrators visibility to all dropped objects in the recycle bin

This view allows administrators to see all deleted objects in the Recycle Bin

One use for these views is to identify the name that the database has assigned to a dropped object, as shown in the following example:

SELECT object_name, original_name FROM dba_recyclebin

WHERE owner = 'HR'

OBJECT_NAME ORIGINAL_NAME

BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES

You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

SQL > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27 purl 1400 purl 19

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0"

Purging Objects in the Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

If you decide not to restore the project from the Recycle Bin, you can use the purge statement to delete the project and its associated objects from the Recycle Bin and free up its storage space. You need to have the same permissions as deleting the project.

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin".

When you use the purge statement to clear a table, you can use the table name in the Recycle Bin or the original name of the table. You can get the Recycle Bin name from the DBA_ or USER_RECYCLEBIN view

The following hypothetical example purges the table hr.int_admin_emp, which was renamed to BIN$jsleilx392mk2=293 $0 when it was placed in the recycle bin:

PURGE TABLE "BIN$jsleilx392mk2=293 $0"

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

PURGE TABLESPACE example

PURGE TABLESPACE example USER oe

Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:

PURGE RECYCLEBIN

If you have the SYSDBA privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.

If you have SYSDBA permission, you can clear the entire Recycle Bin by specifying DBA_RECYCLEBIN instead of RECYCLEBIN in the previous statement.

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.

You can also use the purge statement to clear the index from the Recycle Bin or all objects in the specified tablespace from the Recycle Bin.

Restoring Tables from the Recycle Bin

Use the FLASHBACK TABLE... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. An optional RENAME TO clause lets you rename the table as you recover it. The recycle bin name can be obtained from either the DBA_or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACK TABLE... TO BEFORE DROP statement, you need the same privileges required to drop the table.

The following example restores int_admin_emp table and assigns to it a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP

RENAME TO int2_admin_emp

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of theint2_admin_emp table in the recycle bin and you want to recover the second version. You can do this by issuing two FLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify that you are restoring the correct table.

SELECT object_name, original_name, createtime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME CREATETIME

BIN$yrMKlZaLMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05 purl 21 purl 05purl 52

BIN$yrMKlZaVMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05 purl 2125 purl 13

BIN$yrMKlZaQMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05 purl 22 purl 05purl 53

FLASHBACK TABLE "BIN$yrMKlZaVMhfgNAgAIMenRA==$0" TO BEFORE DROP

Restoring Dependent Objects

When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.

When you restore a table from the Recycle Bin, dependent objects, such as indexes, do not return their original names; they retain the system-generated recycle bin name. You must manually rename the dependent object to restore its original name. If you plan to restore the original name of the dependent object manually, be sure to write down the system-generated recycle bin name for each dependent object before restoring the table.

The following is an example of restoring the original names of some of the indexes of the dropped table JOB_HISTORY, from the HR sample schema. The example assumes that you are logged in as the HR user.

After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

2. SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN

3.

4. OBJECT_NAME ORIGINAL_NAME TYPE

5.-

6. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX INDEX

7. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX INDEX

8. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX INDEX

9. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK INDEX

10. BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY TABLE

Restore the table with the following command:

12. FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP

Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

14. SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY'

15.

16. INDEX_NAME

17.--

18. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0

19. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0

20. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0

21. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0

Restore the original names of the first two indexes as follows:

23. ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX

24. ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX

Note that double quotes are required around the system-generated names.

At this point, the study on the concept and function of the Oracle Recycle Bin is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.