Dump 를 떠서 분석 함 하는중이다.
Undo Block Check
Select s.sid, s.serial#, t.xidusn, t.used_ublk, t.used_urec from v$session s , v$transaction t where t.addr = s.taddr
bdump : background process trace files
cdump : core dump files
udump : user SQL trace files
SQL> run;
1 select DBMS_ROWID.Rowid_Relative_Fno(RowID),
2 DBMS_ROWID.RowID_Block_Number(RowID)
3 from PFC_0405
4* where passengerid = '4263569';
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
61 41060
61 41060
61 41060
61 41060
SQL> conn /as sysdba;
Connected.
SQL> Alter System Dump DataFile 61 Block 41060;
System altered.
EJD@/oracle/app/admin/EJD/udump> tkprof ejd_ora_2150594.trc 2.txt
TKPROF: Release 10.2.0.4.0 - Production on Sun May 1 20:54:46 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
EJD@/oracle/app/admin/EJD/udump>
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.dd26190f
0x02 0x0008.01e.0014bae0 0x00800b24.4e64.17 ---- 4 fsc 0x0000.00000000
0x03 0x0001.02a.0014b983 0x0080000e.4589.0f ---- 4 fsc 0x0000.00000000
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.dd26190f
0x02 0x0008.01e.0014bae0 0x00800b24.4e64.17 ---- 4 fsc 0x0000.00000000
0x03 0x0001.02a.0014b983 0x0080000e.4589.0f --U- 4 fsc 0x0000.e054f874
Select s.sid, s.UserName, s.serial#, --s.Status, S.LockWait, s.Process, s.Machine, s.Module, s.Row_Wait_Obj#, s.Row_Wait_Block#,
t.xidusn, t.xidslot, t.ubablk, t.start_time, t.used_ublk, t.used_urec, t.PHY_IO, t.cr_get, t.start_SCN, t.XID, t.prv_xid, t.ptx_xid
from v$session s , v$transaction t
where t.addr = s.taddr
and s.UserName = 'EJMIS'
and s.SID in (3179, 3220)
order by t.Start_Time
SID USERNAME SERIAL# XIDUSN XIDSLOT
---------- ------------------------------ ---------- ---------- ----------
UBABLK START_TIME USED_UBLK USED_UREC PHY_IO CR_GET
---------- -------------------- ---------- ---------- ---------- ----------
START_SCN XID PRV_XID
---------- -------------------------------- --------------------------------
PTX_XID
--------------------------------
3220 EJMIS 34878 8 30
2852 05/01/11 20:20:03 1 4 0 15
0 0008001E0014BAE0 0000000000000000
0000000000000000
3179 EJMIS 57935 1 42
14 05/01/11 20:30:54 1 4 0 20
3763658809 0001002A0014B983 0000000000000000
0000000000000000
2 rows selected.
Start dump data blocks
Start dump data blocks tsn: 38 file#: 61 minblk 41060 maxblk 41060
buffer tsn: 38 rdba: 0x0f40a064 (61/41060)
scn: 0x0000.e054e370 seq: 0x01 flg: 0x04 tail: 0xe3700601
frmt: 0x02 chkval: 0x6b12 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
*****************************************************************
Dump of memory from 0x0000000110406000 to 0x0000000110408000
110406000 06A20000 0F40A064 E054E370 00000104 [.....@.d.T.p....]
110406010 6B120000 01000000 0002D778 E054E370 [k..........x.T.p]
110406020 00002178 00033200 0F40A061 FFFF0000 [..!x..2..@.a....]
110406030 00000000 00000000 00000000 80000000 [................]
110406040 DD26190F 0008001E 0014BAE0 00800B24 [.&.............$]
110406050 4E641700 00040000 00000000 0001002A [Nd.............*]
110406060 0014B983 0080000E 45890F00 00040000 [........E.......]
110406070 00000000 00000000 00000000 0001003F [...............?]
110406080 FFFF0090 0294038E 038E0000 003F03C5 [.............?..]
110406090 1EBB035F 1DF402FA 1D2F0294 1C681BF0 [..._...../...h..]
1104060A0 1B761AFB 1A961A32 19CD1952 18D7185C [.v.....2...R...\]
1104060B0 17F3178F 172716C2 164815E0 157D1519 [.....'...H...}..]
1104060C0 14B41450 13EB1386 132012B8 125311EF [...P..... ...S..]
1104060D0 118A1124 10BD1056 0FEE0F76 0EFD0E85 [...$...V...v....]
1104060E0 0E0C0D94 0D1B0CA3 0C2A0BB2 0B390AC1 [.........*...9..]
1104060F0 0A4809D0 095708DF 086607EE 077506FD [.H...W...f...u..]
110406100 0684060C 0593051B 04A2042A 00000000 [...........*....]
110406110 00000000 00000000 00000000 00000000 [................]
Repeat 31 times
110406310 2C031505 C4051B24 4702C103 034E4D43 [,......$G....NMC]
110406320 02C10202 C10D02C1 0202C102 034E4D43 [.............NMC]
110406330 03325854 02484B03 4B525702 C302034B [.2XT.HK.KRW....K]
110406340 525702C3 02FF0A53 53522046 6565204F [RW.....SSR Fee O]
110406350 5202C15A 08323031 31303430 3507786F [R..Z.20110405.xo]
110406360 04050E13 18083230 31313034 30350778 [......20110405.x]
110406370 6F04050E 13182C03 1505C405 1B244702 [o.....,......$G.]
110406380 C103034E 4D430180 02C10D02 C10202C1 [...NMC..........]
110406390 02034E4D 43033258 5402484B 034B5257 [..NMC.2XT.HK.KRW]
1104063A0 02C30203 4B525702 C302FF0A 53535220 [....KRW.....SSR ]
*****************************************************************
110407FB0 C10202C1 02034E4D 43FF0248 4B034B52 [......NMC..HK.KR]
110407FC0 5702C302 034B5257 02C302FF 0A535352 [W....KRW.....SSR]
110407FD0 20466565 204F5202 C15A0832 30313130 [ Fee OR..Z.20110]
110407FE0 34303507 786F0405 0E131808 32303131 [405.xo......2011]
110407FF0 30343035 07786F04 050E1318 E3700601 [0405.xo......p..]
Block header dump: 0x0f40a064
Object id on Block? Y
seg/obj: 0x2d778 csc: 0x00.e054e370 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0xf40a061 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.dd26190f
0x02 0x0008.01e.0014bae0 0x00800b24.4e64.17 ---- 4 fsc 0x0000.00000000
0x03 0x0001.02a.0014b983 0x0080000e.4589.0f ---- 4 fsc 0x0000.00000000
data_block_dump,data header at 0x11040607c
===============
tsiz: 0x1f80
hsiz: 0x90
pbl: 0x11040607c
bdba: 0x0f40a064
76543210
flag=--------
ntab=1
nrow=63
frre=-1
fsbo=0x90
fseo=0x294
avsp=0x38e
tosp=0x38e
0xe:pti[0] nrow=63 offs=0
0x12:pri[0] offs=0x3c5
0x14:pri[1] offs=0x1ebb
0x16:pri[2] offs=0x35f
0x18:pri[3] offs=0x1df4
0x1a:pri[4] offs=0x2fa
0x1c:pri[5] offs=0x1d2f
*****************************************************************
0x88:pri[59] offs=0x593
0x8a:pri[60] offs=0x51b
0x8c:pri[61] offs=0x4a2
0x8e:pri[62] offs=0x42a
block_row_dump:
tab 0, row 0, @0x3c5
tl: 101 fb: --H-FL-- lb: 0x2 cc: 21
col 0: [ 5] c4 05 1b 24 46
col 1: [ 2] c1 03
col 2: [ 3] 4e 4d 43
col 3: [ 1] 80
col 4: [ 2] c1 0d
col 5: [ 2] c1 02
col 6: [ 2] c1 02
col 7: [ 3] 4e 4d 43
col 8: [ 3] 54 58 54
col 9: [ 2] 48 4b
col 10: [ 3] 4b 52 57
col 11: [ 2] c3 02
col 12: [ 3] 4b 52 57
col 13: [ 2] c3 02
col 14: *NULL*
col 15: [10] 53 53 52 20 46 65 65 20 4f 52
col 16: [ 2] c1 5a
col 17: [ 8] 32 30 31 31 30 34 30 35
col 18: [ 7] 78 6f 04 05 0e 13 18
col 19: [ 8] 32 30 31 31 30 34 30 35
col 20: [ 7] 78 6f 04 05 0e 13 18
tab 0, row 1, @0x1ebb
tl: 99 fb: --H-FL-- lb: 0x2 cc: 21
col 0: [ 5] c4 05 1b 24 46
col 1: [ 2] c1 02
col 2: [ 3] 4e 4d 43
col 3: [ 1] 80
col 4: [ 2] c1 0d
col 5: [ 2] c1 07
col 6: [ 2] c1 02
col 7: [ 3] 4e 4d 43
col 8: [ 3] 54 58 54
col 9: [ 2] 48 4b
col 10: [ 3] 4b 52 57
col 11: [ 2] c3 02
col 12: [ 3] 4b 52 57
col 13: [ 2] c3 02
col 14: *NULL*
col 15: [ 8] 3f 3f 3f 3f 20 3f 3f 3f
col 16: [ 2] c1 5a
col 17: [ 8] 32 30 31 31 30 34 30 35
col 18: [ 7] 78 6f 04 05 0e 13 18
col 19: [ 8] 32 30 31 31 30 34 30 35
col 20: [ 7] 78 6f 04 05 0e 13 18
tab 0, row 2, @0x35f
tl: 102 fb: --H-FL-- lb: 0x2 cc: 21
col 0: [ 5] c4 05 1b 24 46
col 1: [ 2] c1 03
col 2: [ 3] 4e 4d 43
col 3: [ 2] c1 02
col 4: [ 2] c1 0d
*****************************************************************
col 16: [ 2] c1 5a
col 17: [ 8] 32 30 31 31 30 34 30 35
col 18: [ 7] 78 6f 04 05 0c 23 1d
col 19: [ 8] 32 30 31 31 30 34 30 35
col 20: [ 7] 78 6f 04 05 0c 23 1d
tab 0, row 62, @0x42a
tl: 120 fb: --H-FL-- lb: 0x0 cc: 21
col 0: [ 5] c4 05 27 22 20
col 1: [ 2] c1 03
col 2: [ 3] 43 48 31
col 3: [ 1] 80
col 4: [ 2] c1 06
col 5: [ 2] c1 02
col 6: [ 2] c1 02
col 7: [ 3] 43 48 31
col 8: *NULL*
col 9: [ 2] 48 4b
col 10: [ 3] 4b 52 57
col 11: [ 3] 3d 51 66
col 12: [ 3] 4b 52 57
col 13: [ 3] 3d 51 66
col 14: [20] 30 37 41 70 72 20 5a 45 20 33 30 32 20 43 4a 55 4b 55 56 2f
col 15: [10] 53 53 52 20 46 65 65 20 4f 52
col 16: [ 2] c1 5a
col 17: [ 8] 32 30 31 31 30 34 30 35
col 18: [ 7] 78 6f 04 05 0c 23 1d
col 19: [ 8] 32 30 31 31 30 34 30 35
col 20: [ 7] 78 6f 04 05 0c 23 1d
end_of_block_dump
End dump data blocks tsn: 38 file#: 61 minblk 41060 maxblk 41060
'SQL' 카테고리의 다른 글
| Cost Based Optimizer (0) | 2011.05.08 |
|---|---|
| 액세스 효율의 영향요소 (0) | 2011.05.08 |
| Read Consistency (0) | 2011.05.01 |
| SQLP 2회차 시험후기 (결과 : 합격) (0) | 2011.05.01 |
| 신생 SQLP 자격증 최초 1회차 시험 (결과 : 불합격) (0) | 2011.04.05 |