Oracle deletes a table from the recycle bin

This function was unavailable 10 Gb ago. After deletion, it is unavailable and can only be recovered from backup.

 

# Recording the executed operations in the file, that is, the screen output content

SQL> spool '/home/oracle/drop.txt'

 

 

# Delete an emp table

SQL> drop table emp;

 

Table dropped.

 

# Check that the emp does not exist.

SQL> select * from emp;

Select * from emp

*

ERROR at line 1:

ORA-00942: table or view does not exist

 

# View the content of the recycle bin

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

Emp bin $ sGRMOtFvhCPgQKjABwAOsw ==$ 0 TABLE 2011-10-29: 06: 20: 34

 

# View the content of the user Recycle Bin

SQL> select * from user_recyclebin

2;

 

OBJECT_NAME ORIGINAL_NAME OPERATION

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

TYPE TS_NAME CREATETIME

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

Droptime dropscn PARTITION_NAME CAN

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

RELATED BASE_OBJECT PURGE_OBJECT SPACE

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

BIN $ sGRMOtFvhCPgQKjABwAOsw ==$ 0 EMP DROP

Table users 2005-06-30: 19: 47: 57

2011-10-29: 06: 20: 34 517667 YES

51148 51148 51148 8

 

OBJECT_NAME ORIGINAL_NAME OPERATION

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

TYPE TS_NAME CREATETIME

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

Droptime dropscn PARTITION_NAME CAN

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

RELATED BASE_OBJECT PURGE_OBJECT SPACE

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

BIN $ sGRMOtFuhCPgQKjABwAOsw ==$ 0 PK_EMP DROP

Index users 2005-06-30: 19: 47: 57

2011-10-29: 06: 20: 34 517663 NO YES

51148 51148 51149 8

Restore the recycle bin table to www.2cto.com.

SQL> flashback table emp to before drop;

 

Flashback complete.

 

There will be nothing in the recycle bin.

SQL> show recyclebin

 

Emp can be used again now

SQL> select * from emp where sal> 3000;

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7839 king president 17-NOV-81 5000

10

 

SQL> spool off

[Root @ bogon oracle] #

 

Related Knowledge points:

1. Rename the table when recovering from the recycle bin

SQL> flashback table t2 to before drop rename to t4;

 

2. Delete the table specified by the recycle bin

SQL> purge table t4;

 

3. Clear the recycle bin

SQL> purge recyclebin

 

4. delete a table without going through the recycle bin.

 

SQL> drop table t3 purge;

 

 

 

 

 

 

This article is from the "Technical Exchange" blog