SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database ;
TO_CHAR(SYSDATE,'YY CURRENT_SCN
------------------- -----------
2013-03-31 14:31:05 1051470
SQL> insert into scott.test_query values(1,'John',5000);
1 row created.
SQL> insert into scott.test_query values(2,'William',6000);
1 row created.
SQL> insert into scott.test_query values(3,'Lisa',7000);
1 row created.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database ;
TO_CHAR(SYSDATE,'YY CURRENT_SCN
------------------- -----------
2013-03-31 14:31:32 1051480
SQL> insert into scott.test_query values (4,'Chris',4000);
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database ;
TO_CHAR(SYSDATE,'YY CURRENT_SCN
------------------- -----------
2013-03-31 14:31:59 1051499
SQL> update scott.test_query set salary=0 where name='John';
1 row updated.
SQL> update scott.test_query set salary=10000 where name='Lisa';
1 row updated.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database ;
TO_CHAR(SYSDATE,'YY CURRENT_SCN
------------------- -----------
2013-03-31 14:32:30 1051515
SQL> update scott.test_query set salary=salary*1.1;
4 rows updated.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database ;
TO_CHAR(SYSDATE,'YY CURRENT_SCN
------------------- -----------
2013-03-31 14:33:03 1051590
SQL> select * from scott.test_query as of timestamp to_date('2013-03-31 14:31:05 ','yyyy-mm-dd hh24:mi:ss');
no rows selected
SQL> select * from scott.test_query as of timestamp to_date('2013-03-31 14:33:03
ID NAME SALARY
---------- ---------- ----------
1 John 0
2 William 6600
3 Lisa 11000
4 Chris 4400
SQL> select versions_starttime,versions_operation,versions_xid,id,name,salary fr om scott.test_query versions between scn minvalue and maxvalue where name= 'John ';
VERSIONS_STARTTIME V VE RSIONS_XID ID NAME SALARY
--------------------------------------------------------------------------- - -- -------------- ---------- ---------- ----------
31-MAR-13 02.32.54 PM U 04 0009008D020000 1 John 0
31-MAR-13 02.31.42 PM I 07 00100096020000 1 John 5000
31-MAR-13 02.30.08 PM D 05 0015003C030000 1 John 5000
1 John 5000
SQL> select operation,undo_sql,start_scn,(select name from scott.test_query wher e rowid = flashback_transaction_query.row_id) name from flashback_transaction_qu ery where xid = hextoraw('0700100096020000') order by undo_change#;
OPERATION
--------------------------------
UNDO_SQL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------
START_SCN NAME
---------- ----------
INSERT
delete from "SCOTT"."TEST_QUERY" where ROWID = 'AAATFVAAEAAAAIPAAH';
1051473 Chris
INSERT
delete from "SCOTT"."TEST_QUERY" where ROWID = 'AAATFVAAEAAAAIPAAG';
1051473 Lisa
INSERT
delete from "SCOTT"."TEST_QUERY" where ROWID = 'AAATFVAAEAAAAIPAAF';
1051473 William
INSERT
delete from "SCOTT"."TEST_QUERY" where ROWID = 'AAATFVAAEAAAAIPAAE';
1051473 John
BEGIN
1051473
SQL> select operation,undo_sql,start_scn,(select name from scott.test_query wher e rowid = flashback_transaction_query.row_id) name from flashback_transaction_qu ery where xid = hextoraw('040009008D020000') order by undo_change#;
OPERATION
--------------------------------
UNDO_SQL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------
START_SCN NAME
---------- ----------
UPDATE
update "SCOTT"."TEST_QUERY" set "SALARY" = '4000' where ROWID = 'AAATFVAAEAAAAIP AAH';
1051503 Chris
UPDATE
update "SCOTT"."TEST_QUERY" set "SALARY" = '10000' where ROWID = 'AAATFVAAEAAAAI PAAG';
1051503 Lisa
UPDATE
update "SCOTT"."TEST_QUERY" set "SALARY" = '6000' where ROWID = 'AAATFVAAEAAAAIP AAF';
1051503 William
UPDATE
update "SCOTT"."TEST_QUERY" set "SALARY" = '0' where ROWID = 'AAATFVAAEAAAAIPAAE ';
1051503 John
UPDATE
update "SCOTT"."TEST_QUERY" set "SALARY" = '7000' where ROWID = 'AAATFVAAEAAAAIP AAG';
1051503 Lisa
UPDATE
update "SCOTT"."TEST_QUERY" set "SALARY" = '5000' where ROWID = 'AAATFVAAEAAAAIP AAE';
1051503 John
BEGIN
1051503
7 rows selected.
SQL> update "SCOTT"."TEST_QUERY" set "SALARY" = '5500' where ROWID = 'AAATFVAAEA AAAIPAAE';
1 row updated.
SQL> update "SCOTT"."TEST_QUERY" set "SALARY" = '7700' where ROWID = 'AAATFVAAEA AAAIPAAG';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from scott.test_query where ROWID = 'AAATFVAAEAAAAIPAAG';
ID NAME SALARY
---------- ---------- ----------
3 Lisa 7700
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15874751/viewspace-757500/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15874751/viewspace-757500/