Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management

Flashback technology provides a set of features to view and rewind data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, perform change analysis, and perform self-service repair to recover from logical corruption while the database is online.

Flashback technology provides a SQL interface to quickly analyze and repair human errors. Flashback provides fine-grained analysis and repair for localized damage such as deleting the wrong customer order. Flashback technology also enables correction of more widespread damage, yet does it quickly to avoid long downtime. Flashback technology is unique to the Oracle Database and supports recovery at all levels including row, transaction, table, tablespace, and database.

Oracle Flashback Versions Query is an extension to SQL that can be used to retrieve the versions of rows in a given table that existed in a specific time interval. Oracle Flashback Versions Query returns a row for each version of the row that existed in the specified time interval. For any given table, a new row version is created each time the COMMIT statement is executed.

Oracle Flashback Table enables users to recover a table to a previous point in time. It provides a fast, online solution for recovering a table or set of tables that has been erroneously modified by a user or application. In most cases, Flashback Table alleviates the need for administrators to perform more complicated point-in-time recovery operations. Even after a flashback, the data in the original table is not lost; it can later be reverted back to the original state.

With 9i this feature was marketed as “Oracle found the time machine” :) But with 9i to flashback was not so easy, needed DBA privileges etc. After 10g with SELECT statements we are not able to get only a picture but a movie of our data in time. Ok lets work on an example, seeing is necessary for believing, we are not marketing here :)

create table flashback_test ( c1 number, c2 date ) nologging ;

insert into flashback_test values ( 1, sysdate ) ;
commit ;
exec dbms_lock.sleep(15);

update  flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);

update  flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);

delete flashback_test ;
commit ;
exec dbms_lock.sleep(15);

select versions_starttime, versions_endtime, versions_xid, versions_operation, c1
from  flashback_test versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME ;

VERSIONS_STARTTIME                                VERSIONS_ENDTIME                                  VERSIONS_XID     VERSIONS_OPERATION         C1
------------------------------------------------- ------------------------------------------------- ---------------- ------------------ ----------
08/10/2007 08:18:29                               08/10/2007 08:18:44                               08002200A6040000 U                           2
08/10/2007 08:18:44                               08/10/2007 08:18:59                               03002300C7040000 U                           4
08/10/2007 08:18:59                                                                                 06002900AC040000 D                           4
                                                  08/10/2007 08:18:29                                                                            1

SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('06002900AC040000') ; -- delete

UNDO_SQL
--------------------------------------------------------------------------------
insert into "HR"."FLASHBACK_TEST"("C1","C2") values ('4',TO_DATE('08/10/2007', '

SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('03002300C7040000') ; -- update 4

UNDO_SQL
--------------------------------------------------------------------------------
update "HR"."FLASHBACK_TEST" set "C1" = '2' where ROWID = 'AAAONoAAEAAAAGWAAA';

The maximum of there versions e can get are dependent on UNDO_RETENTION parameter of the database. TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP flashback functions are used in SQL or PL/SQL as needed. In this example we are looking for the sum of the salaries on the employees in time;

set serveroutput on
DECLARE
   l_scn NUMBER;
   l_timestamp TIMESTAMP;
BEGIN
   l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48);
   dbms_output.put_line('l_scn '||l_scn);
   l_timestamp := SCN_TO_TIMESTAMP(l_scn);
   dbms_output.put_line('l_timestamp '||l_timestamp);
END;
/

SELECT sum(salary) FROM employees
AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);

SELECT sum(salary) FROM employees -- TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48)
AS OF TIMESTAMP SCN_TO_TIMESTAMP(1531264);

SQL>

l_scn 1531264
l_timestamp 04/01/2007 08:01:29,000000

PL/SQL procedure successfully completed

SUM(SALARY)
-----------
691400

SUM(SALARY)
-----------
691400

Another related subject is PURGE command and RECYCLEBIN with 10g, after 10g when you drop a table by default it is stored in recyclebin like windows. And you can restore the table with flashback command easily;

drop table RECYCLETEST purge ;
create table RECYCLETEST (c1 number) ;
select * from tab where tname like '%REC%';

-- lets drop the table, this must be seen as an accident :)
drop table recycletest;
select * from tab where tname like '%BIN%';
show recyclebin

-- ok lets restore back
FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
select * from tab where tname like '%REC%';

SQL>

Table dropped

Table created

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

Table dropped

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$6V3qqZH/Q1ascMRsn5uImg==$0 TABLE

Done

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

Similar to windows you may want to free the recyclebin in time.

* For an index, table or tablespace you can use;

PURGE TABLE TEST; or PURGE TABLE “BIN$04LhcpndanfgMAAAAAANPw==$0";
purge index in_test1_01;
PURGE TABLESPACE USERS; or PURGE TABLESPACE USERS USER SCOTT;

* For the all objects belonging to the user ;

PURGE RECYCLEBIN;

* As a DBA all objects;

PURGE DBA_RECYCLEBIN;

Some important notes related to this subject are;

* When you flashback a table the triggers and indexes are not returned with their original names. This can be fixed by running an extra alter index or alter trigger statement.
* Not only you may want to flashback a table that is dropped, like Versions query a whole table can be flashbacked in time;

FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;

* If there are tables with same names in Recyclebin they can be restores with different names;

FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;

FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

* Flashback technology includes the following features:
Oracle Flashback Query
Oracle Flashback Versions Query
Oracle Flashback Transaction Query
Oracle Flashback Table
Oracle Flashback Drop
Oracle Flashback Database — This provides a more efficient alternative to database point-in-time recovery, Check V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views also.
Oracle Flashback Restore Points
* You may want to check the Flashback General and Performance Tips

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

References Used :
Oracle® Database Concepts 10g Release 2 (10.2)

Advertisement