my notes: Flashback Drop

To show recyclebin (current user)

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

WRI$_RCS_22_1 BIN$WU2vaKQiEDfgU244qMDm+g==$0 TABLE 2017-09-16:12:19:38

WRI$_RCS_22_1 BIN$WDGKdHHFD8XgU244qMA7Xw==$0 TABLE 2017-09-02:09:19:48

WRI$_RCS_40_1 BIN$V4cgRZb5PxTgU2U4qMDN1Q==$0 TABLE 2017-08-24:22:02:04

To list a table has been deleted

SELECT * FROM "BIN$WU2vaKQiEDfgU244qMDm+g==$0";

To show parameter about recycle

show parameter recycle

NAME TYPE VALUE

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

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string on

To disable the recycle bin:

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

To enable the recycle bin:

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

To purge objects in the Recycle Bin

PURGE TABLE BIN$jsleilx392mk2=293$0;

PURGE TABLE int_admin_emp;

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

PURGE RECYCLEBIN;

To restore a table from the Recycle Bin

FLASHBACK TABLE print_media TO BEFORE DROP;

To restore and rename a table from the Recycle Bin

FLASHBACK TABLE int_admin_emp TO BEFORE DROP RENAME TO int2_admin_emp;

Restoring Dependent Objects

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;

ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;

Views
USER_RECYCLEBIN

DBA_RECYCLEBIN