Recycle Bin

View All Scripts Login to Run Script

Script Name Recycle Bin
Description Drop and undrop table using flashback and recycle bin. Drop a table, restore a table, then redtop and purge the recycle bin.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1
create a table which we will later drop.
```
create table x (x int)
```
```
Table created.
```
Statement 2
Insert some data to ensure we get our data back when we recover the table from the recycle bin.
```
insert into x values (1)
```
```
1 row(s) inserted.
```
Statement 3
Commit the data to make it permanent in the database.
```
commit
```
```
Statement processed.
```
Statement 4
Validate we have data in our table.
```
select * from x
```
```
 
X
1
```
Statement 5
Now drop the table, which developers can do from time to time by mistake.
```
drop table x
```
```
Table dropped.
```
Statement 6
Attempt to query from the table, since we just dropped the table you should see an error.
```
select * from x
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

X
1

Statement 7

Query the recycle bin to locate the table.

select * from user_recyclebin

 
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
BIN$+wYDLlUrZdDgQwuS5QrsnQ==$0 X DROP TABLE LIVE_SQL_TABLESPACE 2014-06-04:09:40:23 2014-06-04:09:40:23 13627410626480  - YES YES 351937 351937 351937 8

Statement 8
Now flashback the table using the flashback table command.
```
Flashback table x to before drop
```
```
Statement processed.
```
Statement 9
Validate the table has been retrieved from the recycle bin
```
select * from x
```
```
 
X
1
```
Statement 10
Now drop it again to demonstrate how to purge the recycle bin.
```
drop table x
```
```
Table dropped.
```
Statement 11
Purge current schemas recycle bin dropping all tables in recycle bin.
```
begin  
   execute immediate('PURGE RECYCLEBIN'); 
end; 
```
```
Statement processed.
```

OBJECT_NAME	ORIGINAL_NAME	OPERATION	TYPE	TS_NAME	CREATETIME	DROPTIME	DROPSCN	PARTITION_NAME	CAN_UNDROP	CAN_PURGE	RELATED	BASE_OBJECT	PURGE_OBJECT	SPACE
BIN$+wYDLlUrZdDgQwuS5QrsnQ==$0	X	DROP	TABLE	LIVE_SQL_TABLESPACE	2014-06-04:09:40:23	2014-06-04:09:40:23	13627410626480	-	YES	YES	351937	351937	351937	8

X
1

Statement 12

Verify recycle bin is empty.

select * from user_recyclebin

no data found

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database