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;
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;;
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;