flashback transaction query

发布时间：2020-12-15 17:59:01 所属栏目：百科来源：网络整理

导读：闪回事务查询基于事务id查询，可以从version query的versions_xid中获得事务id,从而关联flashback_transaction_query表。 SQL desc flashback_transaction_query; Name???????????? Type?????????? Nullable Default Comments?????????????????????????????

闪回事务查询

基于事务id查询，可以从version query的versions_xid中获得事务id,从而关联flashback_transaction_query表。

SQL> desc flashback_transaction_query;

Name???????????? Type?????????? Nullable Default Comments?????????????????????????????????

---------------- -------------- -------- -------

XID????????????? RAW(8)???????? Y??????????????? Transaction identifier???????????????????

START_SCN??????? NUMBER???????? Y??????????????? Transaction start SCN????????????????????

START_TIMESTAMP? DATE?????????? Y??????????????? Transaction start timestamp??????????????

COMMIT_SCN?????? NUMBER???????? Y??????????????? Transaction commit SCN???????????????????

COMMIT_TIMESTAMP DATE?????????? Y??????????????? Transaction commit timestamp?????????????

LOGON_USER?????? VARCHAR2(30)?? Y??????????????? Logon user for transaction???????????????

UNDO_CHANGE#???? NUMBER???????? Y??????????????? 1-based undo change number???????????????

OPERATION??????? VARCHAR2(32)?? Y??????????????? forward operation for this undo??????????

TABLE_NAME?????? VARCHAR2(256)? Y??????????????? table name to which this undo applies????

TABLE_OWNER????? VARCHAR2(32)?? Y??????????????? owner of table to which this undo applies

ROW_ID?????????? VARCHAR2(19)?? Y??????????????? rowid to which this undo applies?????????

UNDO_SQL???????? VARCHAR2(4000) Y??????????????? SQL corresponding to this undo???????????

SQL> select? timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

?????????? 11357590588766

SQL> delete from flash_table where id=16;

1 row deleted

SQL> commit;

Commit complete

SQL> delete from flash_table

? 2?? where id=17;

1 row deleted

SQL> commit;

Commit complete

SQL> select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

?????????? 11357590590115

SQL>select xid,commit_scn,commit_timestamp,operation,undo_sql

???? from flashback_transaction_query where xid in(

?????????? select versions_xid from flash_table versions between scn 11357590588766 and 11357590590115)；

? ORA-01031: insufficient privileges

用户需要dba角色，或者被授予select any transaction权限。

SQL>grant sleect any transaction to scott;

查看视图，每个事务都对应相同的XID

SQL>Select xid,undo_sql from flashback_transaction_query where xid in (

Select versions_xid from B versions between scn minvalue and maxvalue);

或者

SQL>select xid,undo_sql

from flashback_transaction_query q where q.xid in(select versions_xid from B versions between scn 413946 and 413959);

XID????????????? OPERATION??????????????????????? COMMIT_SCN ?UNDO_SQL

---------------- -------------------------------- ---------- ?----------------------------------

03001C006A020000 DELETE????????????????????????????? 1100723 ??insert into "SYS"."B"("ID") values ('4');

03001C006A020000 DELETE????????????????????????????? 1100723 ??insert into "SYS"."B"("ID") values ('3');

03001C006A020000 DELETE????????????????????????????? 1100723 ??insert into "SYS"."B"("ID") values ('2');

11.2版本中有BUG，UNDO_SQL没有显示

https://forums.oracle.com/forums/thread.jspa?threadID=2455969&tstart=0

SQL> select * from v$version; BANNER ----------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for 64-bit Windows: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> drop table people purge; drop table people purge * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table people(empno number primary key,empname varchar2(16),salary 2 number); Table created. SQL> insert into people values(111,'James',100); 1 row created. SQL> create table dept(deptno number,deptname varchar2(32)); Table created. SQL> insert into dept values(10,'Accounting'); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> update people set salary = salary + 100 where empno = 111; 1 row updated. SQL> insert into dept values(20,'Finance'); 1 row created. SQL> SQL> delete from people where empno=111; 1 row deleted. SQL> SQL> insert into people values(111,'Tom',100); 1 row created. SQL> update people set salary = salary + 100 where empno = 111; 1 row updated. SQL> update people set salary = salary + 50 where empno = 111; 1 row updated. SQL> commit; Commit complete. SQL> SQL> select versions_xid xid,versions_startscn start_scn,versions_endscn 2 end_snc,3 versions_operation operation,empname,salary 4 from people 5 versions between scn minvalue and maxvalue 6 where empno = 111 7 order by start_scn 8 / XID START_SCN END_SNC OP EMPNAME ---------------- ---------- ---------- -- -------------------------------- SALARY ---------- 0900120064060000 2391498 2391514 I James 100 0A000D0068060000 2391514 I Tom 250 0A000D0068060000 2391514 D James 100 1* select undo_sql,OPERATION from flashback_transaction_query where table_name='PEOPLE' order by start_scn SQL> / UNDO_SQL OPERATION -------------------------------------------------- ------------------------------ UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN 6 rows selected.

这是一个 flashback_transaction_query 在11.2的一个BUG

Hdr: 9056188 11.2.0.1.0 RDBMS 11.2.0.1.0 LOGMINER PRODID-5 PORTID-46
Abstract: FLASHBACK_TRANSACTION_QUERY IS NOT SHOWING ANY UNDO_SQL

该BUG似乎一直没有被FIX REPRODUCIBILITY:

Reproduable atwill in 11.2 Not reproducing in 11.1.0.7

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!