谈谈Oracle undo表空间(四)
-- ----------
2 3146
www.2cto.com
这下很清晰的查看到了,是在第2个数据文件的,第3146块上,再查询下第2个数据文件是啥文件
SQL> select tablespace_name,file_id from dba_data_files where file_id=2;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
UNDOTBS1 2
哈,这下更清晰了,那个地址指向的就是undo表空间里面的数据块!
(8)再根据数据文件号和数据块进行dump:
SQL> select tablespace_name,file_id from dba_data_files where file_id=2;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
UNDOTBS1 2
SQL> alter system dump datafile 2 block 3146;
www.2cto.com
System altered.
SQL> SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
2 p.spid || '.trc' trace_file_name
3 FROM (SELECT p.spid
4 FROM v$mystat m, v$session s, v$process p
5 WHERE m.statistic# = 1
6 AND s.SID = m.SID
7 AND p.addr = s.paddr) p,
8 (SELECT t.INSTANCE
9 FROM v$thread t, v$parameter v
10 WHERE v.NAME = 'thread'
11 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
12 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
www.2cto.com
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_4113.trc
(9)查看dump undo出来的文件:
Dump file /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_4113.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: pmiscs
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:12 EDT 2008
Machine: i686 www.2cto.com
Instance name: pmisdb
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 4113, image: oracle@pmiscs (TNS V1-V3)
*** ACTION NAME:() 2012-09-25 13:31:36.874
*** MODULE NAME:(sqlplus@pmiscs (TNS V1-V3)) 2012-09-25 13:31:36.874
*** SERVICE NAME:(SYS$USERS) 2012-09-25 13:31:36.874
*** SESSION ID:(523.77) 2012-09-25 13:31:36.874
Start dump data blocks tsn: 1 file#: 2 minblk 3146 maxblk 3146
buffer tsn: 1 rdba: 0x00800c4a (2/3146)
scn: 0x0000.0117548c seq: 0x01 flg: 0x04 tail: 0x548c0201
frmt: 0x02 chkval: 0x3970 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0E101400 to 0x0E103400
... www.2cto.com
********************************************************************************
UNDO BLK:
xid: 0x0004.005.00000c41 seq: 0x692 cnt: 0x13 irb: 0x13 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f94 0x02 0x1eac 0x03 0x1e04 0x04 0x1d68 0x05 0x1d04
0x06 0x1c68 0x07 0x1c04 0x08 0x1ba8 0x09 0x1b54 0x0a 0x1af8
0x0b 0x1aa4 0x0c 0x1a48 0x0d 0x19f4 0x0e 0x1900 0x0f 0x18b4
0x10 0x17f0 0x11 0x178c 0x12 0x1738 0x13 0x1684
www.2cto.com
*-----------------------------
* Rec #0x1 slt: 0x01 objn: 519(0x00000207) objd: 519 tblspc: 0(0x00000000)
* Layer: 10 (I