Oracle FlashBack Technology
The Oracle Flashback Technology
Oracle Flashback Technology provides some useful ways to get back your lost data without doing whole database or the whole datafile or a large portion of the database restoration from backup database.You do not have to do Point in time recovery. The flashback feature of Oracle 10g is less risk and Efficient of doing table recovery.
Example is from Arjudba Blog
Here is an example for your better understanding,
SQL> select * from t;
A
----------
1
100
10
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
A
----------
1
100
10
2
Flashback Table
SQL> flashback table t to timestamp systimestamp-interval ’1′ minute;
Flashback complete.
SQL> select * from t;
A
----------
1
100
10
Flashback Versions Query
You can also keep track of change data over time suppose when one row is upadted when one row is deleted and when one row is inserted by following query,
select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
Output is,
VERSIONS_STARTT VERSIONS_ENDTIME VERSIONS_XID V A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I 5
.16 PM M
09-APR-08 02.47 0700100035010000 D 5
.26 PM
100
10
1
SQL> insert into t values(200);
1 row created.
SQL> commit;
Commit complete.
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
VERSIONS_STARTT VERSIONS_ENDTIME VERSIONS_XID V A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I 5
.16 PM M
09-APR-08 02.47 0700100035010000 D 5
.26 PM
09-APR-08 02.49 0700140035010000 I 200
.35 PM
10
1
100
6 rows selected.
You can find out the changes over time by ,
select a, versions_starttime, versions_endtime
from t versions between timestamp
to_date(’7/3/20078 13:33:54′,’mm/dd/yyyy hh24:mi:ss’)
and to_date(’7/3/2008 13:37:55′,’mm/dd/yyyy hh24:mi:ss’)
Flashback Transaction Query
You can check undo_sql by,
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = ‘….’;
Flashback Query
SQL> select * from flash;
A
———-
1
2
SQL> insert into flash values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash as of timestamp systimestamp-interval ’1′ minute;
A
———-
1
2
SQL> select * from flash;
A
———-
1
2
3
usefull Links
