Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to drill down on a TX lock
Still have to reply through the news group:
You said:
Hey Jon - more interesting question then before. Oracle told me how to dump
a
block, however reading it is proprietary etc., I have a situation where a
JDBC
thread is attempting to do an insert and is requesting a share lock (4) but
is
being stopped. The table in question (afaik) has an initrans of 1 and
maxtrans of 255. From looking at metalink, running out of ILT slots may
cause
this phenomena. How can I verify whether the block is so full that it
can't
get another slot?
Answer:
connect internal
oradebug setospid (spid value from v$process)
alter system dump processstate 10
NOTE - the block dump in the process state is the same format as the 'ordinary' block dump. Running block dumps after the event can be misleading, as you can find blocks which have one or several ITL entries, and no free space (tosp), but this doesn't prove conclusively that they were the blocks that actually ran into the problem
where: kcbwh1: kcbchg1, why: 0 buffer tsn: 4 rdba: 0x01400cac (5/3244) scn: 0x0000.0108d6e3 seq: 0x00 flg: 0x00 tail: 0xd6e30600 frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataStart of block dump (jpl comment)
--------------------------------------------------
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag LckScn/Fsc
data_block_dump
tsiz: 0x1fb8 hsiz: 0x22 tsiz: 0x1fb8 hsiz: 0x22
flag=----------- ntab=1 nrow=8 frre=-1 fsbo=0x22 fseo=0x22 avsp=0x0 <<< this is what you are after tosp=0x0 <<<--- total free space in block (hex). <<< needs ca. 24 bytes to create ITL 0xe:pti[0] nrow=8 offs=0 0x12:pri[0] offs=0x1bc8 0x14:pri[1] offs=0x17d7 0x16:pri[2] offs=0x13e6 0x18:pri[3] offs=0xff5
-- Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for September 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html Doug C wrote in message ... >I have a feeling this might be somewhat complicated and involve doing block >dumps. Irregardless.. can anyone provide an example? Let's say I've got 4 rows >in a table locked exclusively with a TX lock. What must I do to ascertain which >rows are locked. Dumping every block in the table is acceptable in this >situation because the table is pretty small. Any suggestions, examples? > >Thanks, >DReceived on Mon Aug 06 2001 - 05:01:33 CDT