脚踏实地、勇往直前！
全部博文（1005）
分类： Oracle
2012-11-11 19:39:58
1.创建测试表
SQL> create table tb_test as select username,user_id from dba_users;
Table created.
2.查看表数据
SQL> select * from tb_test;
USERNAME USER_ID
------------------------------ ----------
SYS 0
SYSTEM 5
TEST01 56
CHENLI 60
KETTLE 58
KETTLENEW 59
TEST02 57
HXL 55
OUTLN 11
MGMT_VIEW 53
MDSYS 46
USERNAME USER_ID
------------------------------ ----------
ORDSYS 43
EXFSYS 34
DMSYS 35
DBSNMP 24
WMSYS 25
CTXSYS 36
ANONYMOUS 39
SYSMAN 51
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
USERNAME USER_ID
------------------------------ ----------
OLAPSYS 47
SCOTT 54
TSMSYS 21
MDDATA 50
DIP 19
27 rows selected.
3.对测试表进行相应操作
SQL> delete from tb_test where username='TEST01';
1 row deleted.
SQL> commit;
Commit complete.
SQL> delete from tb_test where username='KETTLE';
1 row deleted.
SQL> commit;
Commit complete.
SQL> update tb_test set user_id = 6 where username = 'CHENLI';
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from tb_test where user_id > 10;
22 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into tb_test values('TEST',8);
1 row created.
SQL> commit;
Commit complete.
4.
SQL> select
2 versions_xid,
3 versions_operation,
4 username,
5 user_id
6 from tb_test versions between timestamp minvalue and maxva
VERSIONS_XID V USERNAME USER_ID
---------------- - ------------------------------ ----------
06002B0034020000 D DIP 19
06002B0034020000 D MDDATA 50
06002B0034020000 D TSMSYS 21
06002B0034020000 D SCOTT 54
06002B0034020000 D OLAPSYS 47
06002B0034020000 D SI_INFORMTN_SCHEMA 45
06002B0034020000 D ORDPLUGINS 44
06002B0034020000 D XDB 38
06002B0034020000 D SYSMAN 51
06002B0034020000 D ANONYMOUS 39
06002B0034020000 D CTXSYS 36
VERSIONS_XID V USERNAME USER_ID
---------------- - ------------------------------ ----------
06002B0034020000 D WMSYS 25
06002B0034020000 D DBSNMP 24
06002B0034020000 D DMSYS 35
06002B0034020000 D EXFSYS 34
06002B0034020000 D ORDSYS 43
06002B0034020000 D MDSYS 46
06002B0034020000 D MGMT_VIEW 53
06002B0034020000 D OUTLN 11
06002B0034020000 D HXL 55
06002B0034020000 D TEST02 57
06002B0034020000 D KETTLENEW 59
VERSIONS_XID V USERNAME USER_ID
---------------- - ------------------------------ ----------
0300270031020000 U CHENLI 6
05000C0039020000 D KETTLE 58
090027004A020000 D TEST01 56
SYS 0
SYSTEM 5
TEST01 56
CHENLI 60
KETTLE 58
KETTLENEW 59
TEST02 57
HXL 55
VERSIONS_XID V USERNAME USER_ID
---------------- - ------------------------------ ----------
OUTLN 11
MGMT_VIEW 53
MDSYS 46
ORDSYS 43
EXFSYS 34
DMSYS 35
DBSNMP 24
WMSYS 25
CTXSYS 36
ANONYMOUS 39
SYSMAN 51
VERSIONS_XID V USERNAME USER_ID
---------------- - ------------------------------ ----------
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
OLAPSYS 47
SCOTT 54
TSMSYS 21
MDDATA 50
DIP 19
01000B00F5010000 I TEST 8
53 rows selected.
5.找到某个XID对应的undo_sql
select xid, undo_sql
from flashback_transaction_query t
where xid = hextoraw('06002B0034020000');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('DIP','19');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('MDDATA','50');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('TSMSYS','21');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('SCOTT','54');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('OLAPSYS','47');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('SI_INFORMTN_SCHEMA','45');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('ORDPLUGINS','44');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('XDB','38');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('SYSMAN','51');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('ANONYMOUS','39');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('CTXSYS','36');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('WMSYS','25');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('DBSNMP','24');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('DMSYS','35');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('EXFSYS','34');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('ORDSYS','43');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('MDSYS','46');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('MGMT_VIEW','53');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('OUTLN','11');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('HXL','55');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('TEST02','57');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('KETTLENEW','59');
06002B0034020000
执行以上语句即可闪回xid=06002B0034020000的事物.
-- The End --
