当前位置:数据库 > Oracle >>

oracle之flashback深入研究

oracle之flashback深入研究
 
 今天是2013-08-24,开始进行oracle flashback 内部原理研究,记录一下笔记。
 
SQL> startup
ORACLE instance started.
 
Total System Global Area  405020672 bytes
Fixed Size                  2213816 bytes
Variable Size             260048968 bytes
Database Buffers          138412032 bytes
Redo Buffers                4345856 bytes
Database mounted.
Database opened.
SQL> 
SQL> select * from t1;
 
        ID        SAL JOB
---------- ---------- ----------
         1          9 z
         1          2 a
         2          3 b
         3          4 c
         1          3 b
         2          1 a
                      D
 
7 rows selected.
 
SQL> select xidusn,ubafil,ubablk from v$transaction;
 
no rows selected
 
SQL> delete from t1 where id=3;
 
1 row deleted.
 
SQL> select xidusn,ubafil,ubablk from v$transaction;
 
    XIDUSN     UBAFIL     UBABLK
---------- ---------- ----------
        17          8       2583
 
SQL> alter system dump datafile 8 block 2583;
 
System altered.
 
SQL> set vlaue for a60
SP2-0158: unknown SET option "vlaue"
SQL> col value for a60
SQL> set linesize 200
SQL> select * from v$diag_info where name='Default Trace File';
 
   INST_ID NAME                                                             VALUE
---------- ---------------------------------------------------------------- ------------------------------------------------------------
         1 Default Trace File                                               /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_1801.trc
 
SQL> commit;
 
Commit complete.
 
 
 
 
UNDO BLK:
xid: 0x0011.019.000000b4  seq: 0x23c cnt: 0x46  irb: 0x46  icl: 0x0   易做图: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f90     0x02 0x1f4c     0x03 0x1ef4     0x04 0x1eb0     0x05 0x1e58
0x06 0x1e14     0x07 0x1dbc     0x08 0x1d78     0x09 0x1d20     0x0a 0x1cdc
0x0b 0x1c84     0x0c 0x1c40     0x0d 0x1be8     0x0e 0x1ba4     0x0f 0x1b4c
0x10 0x1b08     0x11 0x1ab0     0x12 0x19d0     0x13 0x18f0     0x14 0x1868
0x15 0x1800     0x16 0x1794     0x17 0x1620     0x18 0x1508     0x19 0x1480
0x1a 0x1414     0x1b 0x13a8     0x1c 0x1224     0x1d 0x119c     0x1e 0x1134
0x1f 0x10c8     0x20 0x0f4c     0x21 0x0edc     0x22 0x0e54     0x23 0x0de8
0x24 0x0d7c     0x25 0x0d38     0x26 0x0ca0     0x27 0x0c14     0x28 0x0bd0
0x29 0x0b74     0x2a 0x0b18     0x2b 0x0ad4     0x2c 0x0a78     0x2d 0x0a1c
0x2e 0x09d8     0x2f 0x097c     0x30 0x0920     0x31 0x08dc     0x32 0x0880
0x33 0x0824     0x34 0x07dc     0x35 0x0778     0x36 0x071c     0x37 0x06d8
0x38 0x067c     0x39 0x0620     0x3a 0x05dc     0x3b 0x0580     0x3c 0x0524
0x3d 0x04e0     0x3e 0x0484     0x3f 0x0428     0x40 0x0340     0x41 0x02b4
0x42 0x0250     0x43 0x01e8     0x44 0x016c     0x45 0x0114     0x46 0x00d0
 
 
*-----------------------------
* Rec #0x22  slt: 0x19  objn: 468(0x000001d4)  objd: 468  tblspc: 1(0x00000001)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
易做图2: 0
*-----------------------------
uba: 0x02000a17.023c.21 ctl max scn: 0x0000.00a82f35 prv tx scn: 0x0000.00a82f6f
txn start scn: scn: 0x0000.00a83315 logon user: 0
 prev brb: 33580056 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0010.000.000000cb uba: 0x02006433.024f.39
                      易做图: C---    lkc:  0     scn: 0x0000.00a832c7
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0080f7a9  hdba: 0x0080039a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 27(0x1b)
 
SQL> select to_number('a82f35','xxxxxxxxxxxxxxxx') sc from dual;
 
        SC
----------
  11022133
 
SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
   11054748
 
SQL> 
注意:闪回查询和undo_retention有直接关系;
eg:
SQL> update t1 set job='ab' where id=1;
 
3 rows updated.
 
SQL>  select versions_starttime,versions_endtime,versions_xid,versions_operation,id,sal from t1 versions between timestamp minvalue and maxvalue
  2  ;
 
VERSIONS_STARTTIME                                                          VERSIONS_ENDTIME &nb
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,