Recyclebin

Recyclebin

Recyclebin:-When we drop any table from any schema it automatically get attached with recyclebin means not deleted permanently. We can recover that table from recyclebin.

Example:-
DROP TABLE EMP11;  --TABLE WILL BE LINKED TO RECYCLEBIN

Note :- If we want permanent delete then we need to use "purge" keyword with drop statement .

Example:-  
DROP TABLE EMP11 PURGE;       --TABLE WILL BE DROPPED PERMANENTLY

Purging Recyclebin:-When we purge recyclebin , all objects attached with recyclebin will be dropped permanently.

Example:- 
PURGE RECYCLEBIN;    --ALL TABLES FROM RECYCLEBIN WILL BE DROPPED
PURGE DBA_RECYCLEBIN;

Purging Recyclebin Tablespace level:-We can purge recyclebin for particular tablespace. By this statement all tables belonging to that tablespace will purged(Dropped) permanently.

Syntax:- PURGE TABLESPACE ;
Example:- 
PURGE TABLESPACE EXAMPLES1;

Note:- You can understand better with below set of commands.

select * from tab;
alter table DEPT1 move tablespace example;
select table_name,tablespace_name from dba_tables
where owner='SCOTT' and table_name in ('DEPT1','EMP1','EMP22');
drop table DEPT1;
drop table EMP1;
drop table EMP22;
select * from recyclebin;
purge tablespace USERS;
select * from recyclebin;

Purging Recyclebin Tablespace level for single User only:-We can purge recyclebin for tables related to User and Tablespace.

Syntax:- PURGE TABLESPACE USER ;
Example:- 
PURGE TABLESPACE EXAMPLES1 USER SCOTT;

Purging Recyclebin User level:-We can purge recyclebin user level.By this statement all tables of User's those are attached with recyclebin will be purged
 
PURGE USER_RECYCLEBIN;

SELECTING FROM RECYCLEBIN:-We can query from recyclebin just like a  table.

Example:-
SELECT * FROM USER_RECYCLEBIN;
SELECT * FROM DBA_RECYCLEBIN;
SELECT * FROM RECYCLEBIN;
SHOW RECYCLEBIN;