oracle 根据ts闪回表,Oracle 10g 闪回查询特性的增强-根据版本和事务恢复

逃离我推掉我的手 2022-10-21 01:08 160阅读 0赞

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.

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 --

oracle 根据ts闪回表,Oracle 10g 闪回查询特性的增强-根据版本和事务恢复

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 ORACLE表闪回

相关 oracle闪回

相关 oracle11g回闪,Oracle11g开启闪回功能Flashback

相关 oracle 更新后查询事务,Oracle闪回查询，闪回版本查询与闪回事务查询的使用区别总结...

相关 oracle 根据ts闪回表,Oracle 10g 闪回查询特性的增强-根据版本和事务恢复

相关 Oracle 闪回表实验

相关【Oracle】表 drop 后闪回恢复

相关 oracle闪回

相关 oracle闪回技术详解之闪回查询

相关 Oracle 闪回特性详解