Oracle flash Query

New Features of Oracle flash Query

Starting from oracle9i, oracle provides the flashback query feature, allowing data in the rollback segment to be flashed back, the following example shows the new features provided by oracle9i.

SQL> update emp set sal = 4000 where empno = 7788;

 

1 row updated.

SQL> update emp set sal = 4000 where empno = 7782;

 

1 row updated.

 

SQL> update emp set sal = 4000 where empno = 7698;

 

1 row updated.

 

 

 

Do not commit this transaction first. Open a new session in another window and use the sys user to query relevant information for further analysis.

 

Obtain transaction information

You can obtain information about the transaction from the transaction table. The transaction is located in the No. 9 rollback segment (XIDUSN) and on the No. 9 rollback segment.

The transaction is located in the transaction slot No. 29th (XIDSLOT ):

SQL> select xidusn, xidslot, xidsqn, ubablk, ubafil, ubarec from v $ transaction;

 

XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC

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

9 29 385 1350 2 22

You can also obtain transaction information from the v $ rollstat view. The xacts field indicates the number of active transactions.

In rollback segment No. 9

SQL> select usn, writes, rssize, xacts, hwmsize, shrinks, wraps from v $ rollstat;

 

USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS

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

0 7620 385024 0 385024 0 0

1 21390 29351936 0 29351936 0 0

2 22108 3268608 0 3268608 0 0

3 29954 450560 0 450560 0 0

4 23700 843776 0 843776 0 0

5 23334 450560 0 450560 0 0

6 21082 450560 0 450560 0 0

7 23146 2285568 0 2285568 0 0

8 28742 843776 0 843776 0 1

9 22648 2088960 1 2088960 0 0

10 24326 2220032 0 2220032 0 0

 

11 rows selected.

 

This is the execution of alter system dump datafile 2 block 1350

Part of the information about the data blocks in the dump rollback tablespace

*-----------------------------

* Rec # 0x1d slt: 0x24 objn: 517 (0x00000205) objd: 517 tblspc: 0 (0x00000000)

* Layer: 11 (Row) opc: 1 rci 0x00

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

Rdba: 0x00000000

*-----------------------------

Uba: 0x00800546. 0129.1b ctl max scn: 0x0000. 000e4e9c prv tx scn: 0x0000. 000e4ea6

Txn start scn: 0x0000. 000e7526 logon user: 0

Prev brb: 8389956 prev bcl: 0

KDO undo record:

KTB Redo

Op: 0x04 ver: 0x01

Op: L itl: xid: 0x0006. 016.0000015d uba: 0x00800419. 00fe. 11

Flg: C --- lkc: 0 scn: 0x0000. 000e7524

KDO Op code: URP row dependencies Disabled

Xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100f hdba: 0x00401001

Itli: 2 ispac: 0 maxfr: 4863

Tabn: 0 slot: 116 (0x74) flag: 0x2c lock: 0 ckix: 191

Ncol: 9 nnew: 7 size: 0

Vector content:

Col 2: [2] c1 0a

Col 3: [2] c1 0a

Col 4: [1] 80

Col 5: [1] 80

Col 6: [1] 80

Col 7: [1] 80

Col 8: [7] 78 71 01 07 0b 07 34

 

Note that the following information exists: ctl max scn: 0x0000. 000e4e9c. The conversion value to scn is:

SQL> select (to_number ('000', 'xxxx') * power () + to_number ('e4e9c', 'xxxxxxxx') scn

From dual;

 

SCN

----------

937628

 

Query the scn of the current data:

SQL> select dbms_flashback.get_system_change_number scn from dual;

 

SCN

----------

949630

 

SQL>

You can use a specific syntax to query the historical state data of scn 937628:

SQL> select * from scott. emp as of scn 937628 where empno in (7788,7782, 7698 );

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7698 blake manager 7839 1981-5-1 2850.00 30

7782 clark manager 7839 1981-6-9 2450.00 10

7788 scott analyst 7566 1987-4-19 10.00 20

 

SQL>

In the query result, the salary of the three employees is changed to the previous status.

Next 4000:

SQL> select * from scott. emp where empno in (7788,7782, 7698 );

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7698 blake manager 7839 1981-5-1 4000.00 30

7782 clark manager 7839 1981-6-9 4000.00 10

7788 scott analyst 7566 1987-4-19 4000.00 20

1 2 3 4 5 6 Next Page