By Franck Pachot

.
FLASHBACK TABLE restores the latest version that is available in recycle bin. If you did multiple drop / create you may want to restore oldest versions. Of course it’s documented – everything is in the doc. But an example may be useful to understand it before you need it.

Let’s create and drop several times the DEMO table. I change the column name so that I can check easily which one has been restored


20:03:10 SQL> create table DEMO(id number constraint DEMOPK primary key , C1 char );
Table created.
20:03:12 SQL> drop table DEMO;
Table dropped.
20:03:12 SQL> create table DEMO(id number constraint DEMOPK primary key , C2 char );
Table created.
20:03:14 SQL> drop table DEMO;
Table dropped.
20:03:14 SQL> create table DEMO(id number constraint DEMOPK primary key , C3 char );
Table created.
20:03:16 SQL> drop table DEMO;
Table dropped.
20:03:16 SQL> create table DEMO(id number constraint DEMOPK primary key , C4 char );
Table created.
20:03:18 SQL> drop table DEMO;
Table dropped.
20:03:18 SQL> create table DEMO(id number constraint DEMOPK primary key , C5 char );
Table created.
20:03:20 SQL> drop table DEMO;
Table dropped.
20:03:20 SQL> create table DEMO(id number constraint DEMOPK primary key , C6 char );
Table created.
20:03:22 SQL> drop table DEMO;
Table dropped.

Here is what I have in recycle bin


20:03:22 SQL> select object_name,original_name,type,dropscn,createtime,droptime from user_recyclebin order by dropscn;
 
OBJECT_NAME                    ORIGINAL_N TYPE          DROPSCN CREATETIME          DROPTIME
------------------------------ ---------- ---------- ---------- ------------------- -------------------
BIN$KF+J+xYlFjngU3VOqMB9Kw==$0 DEMOPK     INDEX         4350801 2016-01-02:20:03:10 2016-01-02:20:03:12
BIN$KF+J+xYmFjngU3VOqMB9Kw==$0 DEMO       TABLE         4350804 2016-01-02:20:03:10 2016-01-02:20:03:12
BIN$KF+J+xYoFjngU3VOqMB9Kw==$0 DEMOPK     INDEX         4350830 2016-01-02:20:03:12 2016-01-02:20:03:14
BIN$KF+J+xYpFjngU3VOqMB9Kw==$0 DEMO       TABLE         4350833 2016-01-02:20:03:12 2016-01-02:20:03:14
BIN$KF+J+xYrFjngU3VOqMB9Kw==$0 DEMOPK     INDEX         4350857 2016-01-02:20:03:14 2016-01-02:20:03:16
BIN$KF+J+xYsFjngU3VOqMB9Kw==$0 DEMO       TABLE         4350861 2016-01-02:20:03:14 2016-01-02:20:03:16
BIN$KF+J+xYuFjngU3VOqMB9Kw==$0 DEMOPK     INDEX         4350885 2016-01-02:20:03:16 2016-01-02:20:03:18
BIN$KF+J+xYvFjngU3VOqMB9Kw==$0 DEMO       TABLE         4350889 2016-01-02:20:03:16 2016-01-02:20:03:18
BIN$KF+J+xYxFjngU3VOqMB9Kw==$0 DEMOPK     INDEX         4350912 2016-01-02:20:03:18 2016-01-02:20:03:20
BIN$KF+J+xYyFjngU3VOqMB9Kw==$0 DEMO       TABLE         4350915 2016-01-02:20:03:18 2016-01-02:20:03:20
BIN$KF+J+xY0FjngU3VOqMB9Kw==$0 DEMOPK     INDEX         4350939 2016-01-02:20:03:20 2016-01-02:20:03:22
BIN$KF+J+xY1FjngU3VOqMB9Kw==$0 DEMO       TABLE         4350943 2016-01-02:20:03:20 2016-01-02:20:03:22
 
12 rows selected.

and my goal now is to restore a previous version.

issue flashback multiple times

So the documentation tells to issue multiple flashback commands:


20:03:22 SQL> flashback table DEMO to before drop;
Flashback complete.
 
20:03:22 SQL> desc DEMO;
 Name           Null?    Type
 -------------- -------- --------
 ID             NOT NULL NUMBER
 C6                      CHAR(1)

that’s the latest version. Let’s issue the command again:


20:03:22 SQL> flashback table DEMO to before drop;
flashback table DEMO to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

Yes of course, I have to drop it before.


20:03:22 SQL> drop table DEMO;
Table dropped.
 
20:03:22 SQL> flashback table DEMO to before drop;
Flashback complete.
 
20:03:22 SQL> desc DEMO;
 Name           Null?    Type
 -------------- -------- --------
 ID             NOT NULL NUMBER
 C6                      CHAR(1)

Ok, as I dropped it just before, that the latest version that is restored…

purge

If I want to issue multiple flashback table commands, I have to drop purge so that the intermediate restored tables don’t go to recycle bin


20:03:22 SQL> drop table DEMO purge;
Table dropped.
 
20:03:23 SQL> flashback table DEMO to before drop;
Flashback complete.
 
20:03:23 SQL> desc DEMO;
 Name           Null?    Type
 -------------- -------- --------
 ID             NOT NULL NUMBER
 C5                      CHAR(1)

that’s fine: I restored the N-1 version.

rename

The other solution is to restore it to another table, dropping that other table, or changing name each time:


20:03:23 SQL> flashback table DEMO to before drop rename to DEMO1;
Flashback complete.
 
20:03:23 SQL> desc DEMO1;
 Name           Null?    Type
 -------------- -------- --------
 ID             NOT NULL NUMBER
 C4                      CHAR(1)
 
20:03:23 SQL> flashback table DEMO to before drop rename to DEMO2;
Flashback complete.
 
20:03:23 SQL> desc DEMO2;
 Name           Null?    Type
 -------------- -------- --------
 ID             NOT NULL NUMBER
 C3                      CHAR(1)

Here I rewind two older versions.

name the recycle bin object

But there is a direct possibility if you know the version you want from the DBA_RECYCLEBIN view.


20:03:23 SQL> desc "BIN$KF+J+xYpFjngU3VOqMB9Kw==$0"
 Name           Null?    Type
 -------------- -------- --------
 ID             NOT NULL NUMBER
 C2                      CHAR(1)

And I restore that directly to a new table:


20:03:23 SQL> flashback table "BIN$KF+J+xYpFjngU3VOqMB9Kw==$0" to before drop rename to DEMO3;
Flashback complete.

So that’s probably the fastest way to restore an old version.

All that is possible because each time we flashback to before drop, the restored version is removed from the recycle bin.
From my example, only one remains here:


20:03:24 SQL> select object_name,original_name,type,dropscn,createtime,droptime from user_recyclebin order by dropscn;
 
OBJECT_NAME                    ORIGINAL_N TYPE          DROPSCN CREATETIME          DROPTIME
------------------------------ ---------- ---------- ---------- ------------------- -------------------
BIN$KF+J+xYlFjngU3VOqMB9Kw==$0 DEMOPK     INDEX         4350801 2016-01-02:20:03:10 2016-01-02:20:03:12
BIN$KF+J+xYmFjngU3VOqMB9Kw==$0 DEMO       TABLE         4350804 2016-01-02:20:03:10 2016-01-02:20:03:12

so the safest way is probably to flashback to a different table name each time, and clean that only when you’re sure you don’t need them anymore.

Post Views: 382
Thumbnail [60x60]
by
Oracle Team