Useful shortcuts for vi editor

Monday, 8 May 2017

How to use recylebin ?

Check recyclebin is active whether or not
session scope:
SQL>
ALTER SESSION SET recyclebin = ON;

active instance scope:
SQL>
ALTER SYSTEM SET recyclebin = ON;

permanent setting in init.ora file:
*.recyclebin=on

usage of recylebin
SQL>
create table ex_emp as select * from employees;

SQL>
drop table ex_emp;

If you use purge parameter, you cannot take back the table from recyclebin!
-- SQL> drop table ex_emp purge;

check table versions if hr logged in 
select * from USER_RECYCLEBIN order by droptime desc;
-- or
select * from RECYCLEBIN order by droptime desc;;

check table versions if sys logged in
select * from DBA_RECYCLEBIN order by droptime desc;;

Note object_name from result then:
SQL> 
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";

To restore;
SQL>
create table new_ex_emp as 
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";
-- or
SQL>
FLASHBACK TABLE ex_emp 
TO BEFORE DROP 
RENAME TO new_ex_emp;