Restore table and indexes from recycle bin in flashback database

Restore table and indexes from recycle bin in flashback database
-- Recover a dropped database table?
-- In Oracle 10g and later

About topic:
The recyclebin feature introduced in Oracle 10g allows you to recover dropped tables using the flashback table...to before drop command. With recyclebin, Oracle does not automatically delete dropped tables. Instead, Oracle renames dropped and their associated objects, giving them system-generated recyclebin names that begin with BIN$.

For the following examples, consider creating and then dropping this simple table

-- Create table with indexes
create table HR.gouranga(empno number, ename varchar2(10),deptno number(3));
alter table HR.gouranga add constraint c1 PRIMARY KEY(empno) using index;
create index HR.idx_deptno on HR.gouranga(deptno);

-- Insert data into sample table

insert into HR.gouranga values(101,'GOURANGA',10);
insert into HR.gouranga values(102,'MANOJ',20);
insert into HR.gouranga values(103,'BIKASH',20);

-- View the table
SQL> select * from HR.gouranga;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
       101 GOURANGA           10
       102 MANOJ              20
       103 BIKASH             20

-- Verify recyclebin:

$ sqlplus / as sysdba
SQL> desc dba_recyclebin;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE                                               VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME                                           VARCHAR2(19)
 DROPSCN                                            NUMBER
 PARTITION_NAME                                     VARCHAR2(32)
 CAN_UNDROP                                         VARCHAR2(3)
 CAN_PURGE                                          VARCHAR2(3)
 RELATED                                   NOT NULL NUMBER
 BASE_OBJECT                               NOT NULL NUMBER
 PURGE_OBJECT                              NOT NULL NUMBER
 SPACE                                              NUMBER

-- View information from dba_recyclebin

set pagesize 500;
set linesize 120;
column OWNER format a20;
column OBJECT_NAME format a30;
column ORIGINAL_NAME format a40;
column  TYPE format a10;


SQL> select OWNER,OBJECT_NAME,ORIGINAL_NAME,TYPE from dba_recyclebin;

no rows selected

-- Now drop the sample table

SQL> drop table HR.gouranga;

Table dropped.

-- View information from dba_recyclebin

SQL> select OWNER,OBJECT_NAME,ORIGINAL_NAME,TYPE from dba_recyclebin;

OWNER                OBJECT_NAME                    ORIGINAL_NAME                  TYPE
-------------------- ------------------------------ ------------------------------ ----------
HR                   BIN$AECoOodXAMTgUwosCAyTpA==$0 GOURANGA                       TABLE
HR                   BIN$AECoOodWAMTgUwosCAyTpA==$0 C1                             INDEX
HR                   BIN$AECoOodVAMTgUwosCAyTpA==$0 IDX_DEPTNO                     INDEX


-- Find record details from recyclebin object related to sample table.

select * from "HR.BIN$AECoOodXAMTgUwosCAyTpA==$0";


Because a dropped table retains its data, you can easily "undrop" the table by using the flashback table... to before drop command to revert the dropped table to its original name.

Note: Although this command changes the table name, it does not rename any of the dependent objects (e.g., indexes) associated with the table. To revert the dependent objects to their original names, you must manually rename each of them. If you plan to do this, make sure you note the system-generated recyclebin names for each of the dependent objects before you "undrop" the table.

After noting the recyclebin names of any dependent objects in the dropped table, use the following command to restore the table (e.g., change its name back to gouranga):

-- Now flash back the table

SQL> flashback table HR.gouranga to before drop;

Flashback complete.

SQL>

-- Get DDL of hr.GOURANGA after undrop / flashed back

create table hr.GOURANGA
(
  empno  NUMBER not null,
  ename  VARCHAR2(10),
  deptno NUMBER(3)
) tablespace HR_TBLSPC;

alter table hr.GOURANGA
  add constraint BIN$AECoOodUAMTgUwosCAyTpA==$0 primary key (EMPNO);
-- Create/Recreate indexes
create index hr.BIN$AECoOodVAMTgUwosCAyTpA==$0 on hr.GOURANGA (DEPTNO)
  tablespace HR_TBLSPC;

create unique index hr.BIN$AECoOodWAMTgUwosCAyTpA==$0 on hr.GOURANGA (EMPNO)
  tablespace HR_TBLSPC;

To revert the indexes to their original names, you must use the following command for each index:

sql> alter index "recyclebin_name" rename to original_name;

-- Rename Indexes

alter index "hr.BIN$AECoOodVAMTgUwosCAyTpA==$0" rename to idx_deptno;

OR

alter table hr.GOURANGA drop constraint "BIN$AECoOodUAMTgUwosCAyTpA==$0" cascade;

-- Create/Recreate primary, unique and foreign key constraints
alter table hr.GOURANGA add constraint gou_pk primary key (EMPNO);

-- Drop indexes 
drop index "hr.BIN$AECoOodVAMTgUwosCAyTpA==$0";

-- Create/Recreate indexes 
create unique index hr.idex_gou on hr.GOURANGA (EMPNO) tablespace HR_TBLSPC


Crossverify data after undrop / flash back:

SQL> select * from hr.gouranga;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
       101 GOURANGA           10
       102 MANOJ              20
       103 BIKASH             20


When a table is dropped, it is only renamed, not deleted. It remains part of your tablespace and counts against your user tablespace quota. To reclaim tablespace, use flashback to restore tables, or use purge to clear them from recyclebin; for example:

SQL> purge table "BIN$AECoOodXAMTgUwosCAyTpA==$0" ;

  Table purged.

Otherwise, objects will remain in recyclebin until either the tablespace is exhausted or your user quota on the tablespace is met. In either case, Oracle will begin purging objects one at a time, starting with those that have been kept in recyclebin the longest, until it creates enough space for the current operation.

--- Issues may be faced.

SQL> drop index "hr.BIN$AECoOodVAMTgUwosCAyTpA==$0";
drop index "hr.BIN$AECoOodVAMTgUwosCAyTpA==$0"
           *
ERROR at line 1:
ORA-00972: identifier is too long

Wait my next publish for this solution...