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
上一个:x$bh详解
下一个:oracle之监听保护
- 更多Oracle疑问解答:
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有,生产Oracle Rman 备份脚本的工具啊!
- 初学orcle,希望有大大帮忙解说一下详细步骤,从登录oracle到创建表的过程
- oracle语句问题:一张user表,三个字段,id,name,time,插入记录比如:张三2007,李四2008,张三2011
- 如何写一个ORACLE触发器同步两个表中的数据?
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G,在RMAN中如何按指定的时间恢复数据文件啊?
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊?