flashback table in 10g

Flashback technology was introduced in 9i with flashback queries. In 10g, it has evolved significantly with the introduction of flashback table and even flashback database. The flashback table concept is simple. We can now flashback a table to either a previous state in time or to a time before it was dropped. We will see examples of both methods of flashback table in this article.

setup and prerequisites

We'll use a very simple table for our examples as follows.

SQL> CREATE TABLE t
  2  NOLOGGING
  3  ENABLE ROW MOVEMENT
  4  AS
  5  	  SELECT object_type AS x
  6  	  FROM   all_objects;

Table created.

As stated, we'll be exploring both forms of flashback table in this article. To flashback an existing table to a prior state, there are two pre-requisites as follows.

To flashback a dropped table, all that is required is the privilege that enabled us to drop the table in the first place.

Having created our table, we should wait a few minutes to allow the SCN to advance. Attempting to flashback a new table straight away can result in ORA-01466: unable to read data - table definition has changed.

flashback to a previous state

Flashback table is primarily a recovery technology so we'll demonstrate the recovery of data that was deleted from our table and committed. We'll start with the deletion.

SQL> DELETE
  2  FROM   t
  3  WHERE  x = 'PACKAGE';

863 rows deleted.

We'll commit this DML and confirm that the data has gone.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*)
  2  FROM   t
  3  WHERE  x = 'PACKAGE';

  COUNT(*)
----------
         0

1 row selected.

To recover our data, we can flashback the table to its state before the delete took place. Unlike flashback query, which simply reports the data at a fixed point in time, flashback table permanently recovers the data. This cannot be undone (other than by repeating the DML that changed the data in the first place, that is). In the following example, we'll pick a time just two minutes previously and verify that our data has been recovered.

SQL> FLASHBACK TABLE t TO TIMESTAMP SYSTIMESTAMP - INTERVAL '2' MINUTE;

Flashback complete.

SQL> SELECT COUNT(*)
  2  FROM   t
  3  WHERE  x = 'PACKAGE';

  COUNT(*)
----------
       863

1 row selected.

We can also flashback the table to a specific SCN. Oracle supplies two functions to assist with SCN and TIMESTAMP conversions: TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP. In fact, for data migrations and releases, it is probably sensible to record either the SCN or TIMESTAMP prior to changing the data. In the following example, we'll repeat the deletion but record the SCN in a bind variable up-front and use this in our recovery.

SQL> var scn number;
SQL> BEGIN
  2     :scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> DELETE
  2  FROM   t
  3  WHERE  x = 'PACKAGE';

863 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*)
  2  FROM   t
  3  WHERE  x = 'PACKAGE';

  COUNT(*)
----------
         0

1 row selected.

This time we can use the SCN to drive our recovery. The following demonstrates that we can use bind variables in the FLASHBACK TABLE command as well as literals (we used literals in the TIMESTAMP example).

SQL> FLASHBACK TABLE t TO SCN :scn;

Flashback complete.

SQL> SELECT COUNT(*)
  2  FROM   t
  3  WHERE  x = 'PACKAGE';

  COUNT(*)
----------
       863

1 row selected.

flashback data, not structure

It is worth noting that flashback table restores data, not table structure. The exception to this is the before drop option that we will see later in this article (which "undrops" a dropped table). This aside, we can easily demonstrate the effects of flashback table to a time before a table alteration occurred. In the following example, we will add a new populated column to our scratch table and flashback to a time before the DDL took place.

First we'll modify the scratch table as follows.

SQL> ALTER TABLE t ADD new_column INTEGER DEFAULT 0;

Table altered.

SQL> UPDATE t SET x = 'MODIFIED';

48418 rows updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t WHERE ROWNUM <= 10;

X                   NEW_COLUMN
------------------- ----------
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0
MODIFIED                     0

10 rows selected.

We now have a table and data that is very different from the time of our restore point. In addition to having modified data in an existing column, we have an entirely new column. We can now flashback this table and see the effects, given that it now has a different structure.

SQL> FLASHBACK TABLE t TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE);

Flashback complete.

SQL> SELECT * FROM t WHERE ROWNUM <= 10;

X                   NEW_COLUMN
------------------- ----------
TABLE
INDEX
VIEW
LIBRARY
PACKAGE BODY
VIEW
VIEW
VIEW
VIEW
VIEW

10 rows selected.

We can see that the original data is restored but the new column has not been removed (although its data has). This raises some interesting questions about how Oracle implements the flashback table feature. The clues are both in our schema and in SQL trace. If we look at the objects created during this article, we can see an additional table that we did not create ourselves.

SQL> SELECT owner
  2  ,      object_name
  3  ,      object_type
  4  ,      temporary
  5  FROM   dba_objects
  6  WHERE  created > TRUNC(SYSDATE);

OWNER      OBJECT_NAME          OBJECT_TYPE        TEMPORARY
---------- -------------------- ------------------ ----------
SCOTT      SYS_TEMP_FBT         TABLE              Y
SCOTT      T                    TABLE              N
  
2 rows selected.

We can see a global temporary table named SYS_TEMP_FBT. Oracle has created this table to support our flashback table operation (we'll investigate why in a moment). Its structure is as follows.

SQL> desc SYS_TEMP_FBT
 Name                              Null?    Type
 -------------------------------- --------- ----------------
 SCHEMA                                     VARCHAR2(32)
 OBJECT_NAME                                VARCHAR2(32)
 OBJECT#                                    NUMBER
 RID                                        ROWID
 ACTION                                     CHAR(1)

A small sample of the data in this system-generated table is as follows.

SQL> SELECT * FROM sys_temp_fbt SAMPLE (0.01);

SCHEMA     OBJECT_NAME             OBJECT# RID                  ACTION
---------- -------------------- ---------- -------------------- -------
SCOTT      T                         53427 AAANCzAAEAAAAAMAJZ   D
SCOTT      T                         53427 AAANCzAAEAAAAAXAAX   I
SCOTT      T                         53427 AAANCzAAEAAAAAkACS   D
SCOTT      T                         53427 AAANCzAAEAAAAArAGT   D
SCOTT      T                         53427 AAANCzAAEAAAAAwAEV   D
SCOTT      T                         53427 AAANCzAAEAAAAF0AA7   D
SCOTT      T                         53427 AAANCzAAEAAAAGYAE7   I
SCOTT      T                         53427 AAANCzAAEAAAAGvAA8   I
SCOTT      T                         53427 AAANCzAAEAAAAHwAH4   D

9 rows selected.

This data appears when we perform a flashback table operation. In fact, the table itself is created on our first flashback table operation. From the above, it appears as though this table is recording the removal of our "new" data and the re-instatement of our existing data. We can draw this conclusion because we have the same number of deletes (action=D) and inserts (action=I) as follows.

SQL> SELECT action
  2  ,      COUNT(*)
  3  FROM   sys_temp_fbt
  4  GROUP  BY
  5         action;

ACTION       COUNT(*)
---------- ----------
D               48418
I               48418

2 rows selected.

An examination of the SQL trace file shows the following statements.


********************************************************************************

truncate table sys_temp_fbt

********************************************************************************

INSERT /*+ APPEND */ into SYS_TEMP_FBT SELECT /*+ FBTSCAN FULL(S) PARALLEL(S, 
  DEFAULT) */ :1, :2, :3, rowid, SYS_FBT_INSDEL FROM SCOTT.T as of SCN :4 S

********************************************************************************

DELETE /*+ BYPASS_UJVC */ FROM (SELECT /*+ ORDERED USE_NL(S) PARALLEL(S, 
  DEFAULT) PARALLEL(T, DEFAULT) */ S.rowid FROM SYS_TEMP_FBT T, SCOTT.T S 
WHERE
 T.rid = S.rowid and T.action = 'D' and T.object#  = : 1) V

********************************************************************************

INSERT /*+ PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ INTO SCOTT.T SELECT 
  /*+ USE_NL(S) ORDERED PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ S.* FROM 
  SYS_TEMP_FBT T , SCOTT.T as of SCN :1 S WHERE T.rid = S.rowid and T.action =
   'I' and T.object# = :2 

********************************************************************************

The above SQL statements are arranged in order of their appearance in the trace file. It is interesting to note how Oracle is implementing flashback table. The sequence of events is something like the following:

In step 3 above, Oracle uses S.* as the column-assignment for the insert into T. At such time, the column NEW_COLUMN was non-existent, so it is clear that flashback query returns NULL rather than creates an exception such as the ORA-01466 error described in the introduction to this article.

As an aside, it's also interesting to note the appearance of the SYS_FBT_INSDEL function and FBTSCAN hint, but these are investigations for another day!

flashback drop

In addition to recovering data in a table, we can also recover a dropped table. We'll see an example of this first, then explore how Oracle makes this possible. First we'll drop the demonstration table.

SQL> DROP TABLE t;

Table dropped.

To recover this table, we use the third and final option to FLASHBACK TABLE, this being TO BEFORE DROP.

SQL> FLASHBACK TABLE t TO BEFORE DROP;

Flashback complete.

SQL> DESCRIBE t;
 Name                              Null?    Type
 --------------------------------- -------- ---------------
 X                                          VARCHAR2(19)
 NEW_COLUMN                                 NUMBER(38)

How many DBAs or developers wish that this had been available in previous releases following an accident? This author is certainly one of them!

So how does Oracle enable flashback drop? The answer is that Oracle 10g now has a recycle bin (the recycle bin concept should be familiar to anyone who has ever used Windows). We'll take a short look at this in the next section.

recycle bin

In 10g, whenever we drop a table using the existing DROP TABLE syntax, Oracle doesn't actually drop the segment (or segments as FLASHBACK TABLE also works with partitioned tables). Rather, the segment is renamed and the object recorded in the recycle bin. We can see the contents of the recycle bin in the XXX_RECYCLEBIN view family, but we also have a RECYCLEBIN synonym for the USER view. The USER_RECYCLEBIN view looks as follows.

SQL> DESC recyclebin
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 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

Let's drop the demonstration table again. Using Tom Kyte's print_table procedure to make the format easier to read, we can see our table in the recycle bin. Note the system-generated name (you might have noticed names like these appearing in your XXX_OBJECTS views).

SQL> DROP TABLE t;

Table dropped.

SQL> exec print_table( q'[SELECT * FROM recyclebin WHERE original_name = 'T']' );

OBJECT_NAME                   : BIN$KGpN+vdaIL/gRAADukiYGw==$0
ORIGINAL_NAME                 : T
OPERATION                     : DROP
TYPE                          : TABLE
TS_NAME                       : USERS
CREATETIME                    : 2004-06-05:11:50:04
DROPTIME                      : 2004-06-05:14:29:16
DROPSCN                       : 1434683
PARTITION_NAME                :
CAN_UNDROP                    : YES
CAN_PURGE                     : YES
RELATED                       : 53427
BASE_OBJECT                   : 53427
PURGE_OBJECT                  : 53427
SPACE                         : 256
-----------------

PL/SQL procedure successfully completed.

The recycle bin is telling us that we can recover this table and that the segment is currently using 256 blocks of space. Which leads us to consider space issues. If we genuinely have no further need for this table we can purge the entire recycle bin using the PURGE RECYCLEBIN command. This will permanently drop all of the segments in the recycle bin and release the space back to the respective tablespaces. If we choose not to do anything with the recycle bin segments and run out of space in a tablespace, Oracle will automatically reclaim some space by purging relevant segments itself.

As we have seen, Oracle has changed the behaviour of the DROP TABLE command. If we do not wish to preserve the table in the recycle bin, we can permanently drop it by adding the PURGE keyword to the end of the DDL. In the following example, we'll recover the demonstration table, drop it permanently and then check the contents of the recycle bin as before. First, we'll undrop the table but this time we'll include another little feature that enables us to rename the recovered table.

SQL> FLASHBACK TABLE t TO BEFORE DROP RENAME TO t_renamed;

Flashback complete.

SQL> DESCRIBE t_renamed;

Name                              Null?    Type
 --------------------------------- -------- ---------------
 X                                          VARCHAR2(19)
 NEW_COLUMN                                 NUMBER(38)

Finally, we'll permanently drop the table and check the recycle bin.

SQL> DROP TABLE t_renamed PURGE;

Table dropped.

SQL> exec print_table( q'[SELECT * FROM recyclebin WHERE original_name = 'T_RENAMED']' );

PL/SQL procedure successfully completed.

a warning on indexes and constraints

When a table is dropped without purge, any additional segments "belonging" to the table (such as indexes or LOBs) are also renamed and recorded in the recycle bin. However, when the table is recovered, the additional segments are not renamed (surely this behaviour will not be around for too many releases). We can see this in the following example, where we'll begin by creating a table with a primary key.

SQL> CREATE TABLE t (x INT PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX it ON t(x)));

Table created.

With this demonstration table we have two segments; the table and the index that supports the primary key. Now we'll drop the table and look in the recycle bin.

SQL> DROP TABLE t;

Table dropped.

SQL> SELECT r.object_name
  2  ,      r.original_name
  3  ,      r.type
  4  FROM   user_recyclebin r
  5  WHERE  r.base_object = (SELECT rr.base_object
  6                          FROM   user_recyclebin rr
  7                          WHERE  rr.original_name = 'T');

OBJECT_NAME                         ORIGINAL_NAME   TYPE
----------------------------------- --------------- ------------
BIN$KG36on7jNr3gRAADukiYGw==$0      IT              INDEX
BIN$KG36on7kNr3gRAADukiYGw==$0      T               TABLE

2 rows selected.

We can see that Oracle has recorded both segments in the recycle bin, but the following demonstrates what happens when we recover the table.

SQL> FLASHBACK TABLE t TO BEFORE DROP;

Flashback complete.

SQL> SELECT index_name
  2  FROM   user_indexes
  3  WHERE  table_name = 'T';

INDEX_NAME
------------------------------
BIN$KG36on7jNr3gRAADukiYGw==$0

1 row selected.

SQL> SELECT constraint_name
  2  FROM   user_constraints
  3  WHERE  table_name = 'T'
  4  AND    constraint_type = 'P';

CONSTRAINT_NAME
------------------------------
BIN$KG36on7iNr3gRAADukiYGw==$0

1 row selected.

This is unlikely to be a feature-killer for most developers or DBAs, as indexes and constraints can be renamed after the event (as long as we keep a note of their recycle bin and original name pairs). It would, however, be worth keeping a close check on patchsets to see if Oracle changes this behaviour.

further reading

For further information, see the online documentation.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, June 2004

Back to Top