Flashback Queries
Flashback features were there in 9i. 10g is providing something new/extra.
### From new features guide ###
What if you ever need to do a resetlogs operation for recovery purposes, you can take the
database back to a point in time before the resetlogs if you find that you made a
mistake. This provides administrators more flexibility to detect and correct human
error situations.
It is now possible to flashback the primary and standby databases to an SCN or a point in
time prior to the switchover operation. Using flashback in this way on a physical standby
database preserves the standby role. Using flashback in this way on a logical standby
database changes the role of the standby database to what it was at the target SCN/time.
### ###
Here is how you can use Flashback options.
SQL> create table ksdesai.fstes (a number(2));Table created.
SQL> insert into ksdesai.fstes values (1);
1 row created.
SQL> insert into ksdesai.fstes values (2);
1 row created.
SQL> insert into ksdesai.fstes values (3);
1 row created.
SQL> insert into ksdesai.fstes values (4);
1 row created.
SQL> insert into ksdesai.fstes values (5);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ksdesai.fstes;
A
----------
1
2
3
4
5
SQL> delete from ksdesai.fstes where a<3;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT versions_xid XID, versions_startscn START_SCN,
2 versions_endscn END_SCN, versions_operation OPERATION,
3 A AS TABLE_COLUMN FROM KSDESAI.FSTES
4 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
5 ;\
6
SQL> SELECT versions_xid XID, versions_startscn START_SCN,
2 versions_endscn END_SCN, versions_operation OPERATION,
3 A AS TABLE_COLUMN FROM KSDESAI.FSTES
4 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
XID START_SCN END_SCN O TABLE_COLUMN
---------------- ---------- ---------- - ------------
000A001B00078D60 660961162 D 2
000A001B00078D60 660961162 D 1
0005002E0007F815 660961134 I 5
0005002E0007F815 660961134 I 4
0005002E0007F815 660961134 I 3
0005002E0007F815 660961134 660961162 I 2
0005002E0007F815 660961134 660961162 I 1
7 rows selected.
SQL> insert into ksdesai.fstes
2 select a from ksdesai.fstes
3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
4 where versions_operation='D';
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from ksdesai.fstes;
A
----------
3
4
5
2
1
Magical!!! Or is it?
Kirtan
####UPDATES####
Retriving delete records from a table within certain period.
SQL> create table t1 (a number);
Table created.
SQL> desc t1;
Name Null? Type
------ ------ --------
A NUMBER
SQL> insert into t1 values (1);
1 row created.
SQL> insert into t1 values (2);
1 row created.
SQL> insert into t1 values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
1
2
3
SQL> create table t3 (a number);
Table created.
SQL> insert into t3 values (1);
1 row created.
SQL> insert into t3 values (2);
1 row created.
SQL> insert into t3 values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t3;
A
----------
1
2
3
######################
SQL> delete from t1 where a=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
1
3
SQL> select * from t3;
A
----------
1
2
3
########################
SQL> select versions_xid XID, versions_startscn START_SCN,
2 versions_endscn END_SCN, versions_operation OPERATION,
3 A AS TABLE_COLUMN from t1
4 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
XID START_SCN END_SCN O TABLE_COLUMN
---------------- ---------- ---------- - ------------
000B000D0006B0E0 661139198 D 2
000B002C0006B412 661139095 I 3
000B002C0006B412 661139095 661139198 I 2
000B002C0006B412 661139095 I 1
######################
SQL> select * from t3;
A
----------
1
2
3
SQL> delete from t3 where a in (
2 select a from t1
3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
4 where
5 versions_operation='D'
6 );
1 row deleted.
SQL> select * from t3;
A
----------
1
3
#######################
SQL> select * from t1 a, t1 b where a.a=b.a;
A A
---------- ----------
1 1
3 3
######################
SQL> insert into t1
2 select a from t1
3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
4 where
5 versions_operation='D';
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
1
3
2
######################
This entry was posted on Wednesday, April 4th, 2007 at 8:46 pm and is filed under Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.