alter system dump datafile block示例
查看当前数据所在数据文件和数据块
执行dump命令
查看dump出来的trace文件
总结:用alter system dump命令可以dump出oracle的文件和块的内容,这对于我们研究和理解oracle的内部结构有很大好处。
查看当前数据所在数据文件和数据块
- > select * from tt;
-
- A
- ----------
- a
- 2
-
- > select dbms_rowid.rowid_block_number(rowid) bl,dbms_rowid.rowid_relative_fno(rowid) fno from tt;
-
-
- BL FNO
- ---------- ----------
- 182 4
- 182 4
- > alter system dump datafile 4 block 182;
-
- System altered.
#必须拥有sysdba权限用户执行
- > oradebug setmypid;
-
- Statement processed.
- #查看trace文件路径
- > oradebug tracefile_name;
-
- /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_7427.trc
- [oracle@uumile ~]$ more /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_7427.trc
- Trace file /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_7427.trc
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
- ORACLE_HOME = /s01/oracle/app/oracle/product/11.2.0.4/dbhome_1 #操作系统的一些基本信息
- System name: Linux
- Node name: uumile
- Release: 2.6.18-308.el5
- Version: #1 SMP Fri Jan 27 17:17:51 EST 2012
- Machine: x86_64
- Instance name: test
- Redo thread mounted by this instance: 1
- Oracle process number: 18
- Unix process pid: 7427, image: oracle@uumile (TNS V1-V3)
-
- *** 2015-03-27 15:58:17.941 #执行dump的会话信息
- *** SESSION ID:(1.21) 2015-03-27 15:58:17.941
- *** CLIENT ID:() 2015-03-27 15:58:17.941
- *** SERVICE NAME:(SYS$USERS) 2015-03-27 15:58:17.941
- *** MODULE NAME:(sqlplus@uumile (TNS V1-V3)) 2015-03-27 15:58:17.941
- *** ACTION NAME:() 2015-03-27 15:58:17.941
- Start dump data blocks tsn: 4 file#:4 minblk 182 maxblk 182
- Block dump from cache:
- Dump of buffer cache at level 4 for tsn=4 rdba=16777398
- BH (0x85fdd170) file#: 4 rdba: 0x010000b6 (4/182) class: 1 ba: 0x85cbc000
- set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 417,19
- dbwrid: 0 obj: 15054 objn: 15054 tsn: 4 afn: 4 hint: f
- hash: [0x85fdf1d8,0x90e6bee0] lru: [0x85fdd398,0x85fdd128]
- ckptq: [NULL] fileq: [NULL] objq: [0x85fdd3c0,0x85fdd150] objaq: [0x85fdd3d0,0x85fdd160]
- st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 4
- flags: block_written_once redo_since_read
- LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
- BH (0x85fdf120) file#: 4 rdba: 0x010000b6 (4/182) class: 1 ba: 0x85cf0000
- set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 417,19
- dbwrid: 0 obj: 15050 objn: 15050 tsn: 4 afn: 4 hint: f
- hash: [0x85fe76b0,0x85fdd228] lru: [0x85fdf348,0x85fdf0d8]
- lru-flags: on_auxiliary_list
- ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
- st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33
- flags:
- BH (0x85fe75f8) file#: 4 rdba: 0x010000b6 (4/182) class: 1 ba: 0x85dca000
- set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 417,19
- dbwrid: 0 obj: 15035 objn: 15035 tsn: 4 afn: 4 hint: f
- hash: [0x873d98e0,0x85fdf1d8] lru: [0x85fe7820,0x85fe75b0]
- lru-flags: on_auxiliary_list
- ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
- st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0 lfb: 33
- flags:
- Block dump from disk:
- buffer tsn: 4 rdba: 0x010000b6 (4/182)
- scn: 0x0000.000940e1 seq: 0x01 flg: 0x06 tail: 0x40e10601
- frmt: 0x02 chkval: 0x138d type: 0x06=trans data
- Hex dump of block: st=0, typ_found=1
- Dump of memory from 0x00002AC9C7033A00 to 0x00002AC9C7035A00
- 2AC9C7033A00 0000A206 010000B6 000940E1 06010000 [.........@......]
- 2AC9C7033A10 0000138D 00000001 00003ACE 000940AC [.........:...@..]
- 2AC9C7033A20 00000000 00320002 010000B0 00050008 [......2.........]
- 2AC9C7033A30 0000012E 00C007DF 002E0043 00002002 [........C.... ..]
- 2AC9C7033A40 000940E1 00000000 00000000 00000000 [.@..............]
- 2AC9C7033A50 00000000 00000000 00000000 00000000 [................]
- 2AC9C7033A60 00000000 00020100 0016FFFF 1F701F8E [..............p.]
- 2AC9C7033A70 00001F70 1F930002 00001F8E 00000000 [p...............]
- 2AC9C7033A80 00000000 00000000 00000000 00000000 [................]
- Repeat 502 times
- 2AC9C70359F0 012C0000 2C320101 61010101 40E10601 [..,...2,...a...@]
- Block header dump: 0x010000b6
- Object id on Block? Y
- seg/obj: 0x3ace csc: 0x00.940ac itc: 2 flg: E typ: 1 - DATA
- brn: 0 bdba: 0x10000b0 ver: 0x01 opc: 0
- inc: 0 exflg: 0
- Itl Xid Uba Flag Lck Scn/Fsc
- 0x01 0x0008.005.0000012e 0x00c007df.0043.2e --U- 2 fsc 0x0000.000940e1
- 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
- bdba: 0x010000b6
- data_block_dump,data header at 0x2ac9c7033a64
- ===============
- tsiz: 0x1f98
- hsiz: 0x16
- pbl: 0x2ac9c7033a64
- 76543210
- flag=--------
- ntab=1
- nrow=2
- frre=-1
- fsbo=0x16
- fseo=0x1f8e
- avsp=0x1f70
- tosp=0x1f70
- 0xe:pti[0] nrow=2 offs=0
- 0x12:pri[0] offs=0x1f93
- 0x14:pri[1] offs=0x1f8e
- block_row_dump: #表中存放的数据的信息
- tab 0, row 0, @0x1f93
- tl: 5 fb: --H-FL-- lb: 0x1 cc: 1
- col 0: [ 1] 61
- tab 0, row 1, @0x1f8e
- tl: 5 fb: --H-FL-- lb: 0x1 cc: 1
- col 0: [ 1] 32
- end_of_block_dump
- End dump data blocks tsn: 4 file#: 4 minblk 182 maxblk 182
总结:用alter system dump命令可以dump出oracle的文件和块的内容,这对于我们研究和理解oracle的内部结构有很大好处。