Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Maximum number of transaction. (Newby question).
I forgot to say that , although you are correct, I think that for someone
who has 'begun playing around with an Oracle database' the statement that
"it can be easily verified by dumping the datablock" is a little bit
misleading. For what its worth I did the experiment you describe
create table test (id number,col1 char(10)) initrans 1 maxtrans 1; insert 3 rows and commit;
session 1 update where id=1, session 2 update where id=2, session 3 update where id=3 -- this session hangs.
session 4 ! run
select header_file,header_block+1 from dba_segments where segment_name='TEST';
get 1,52858 (oops created in system tablespace bad boy Niall).
run alter system dump datafile 1 block 52858; get the following in the trace file
buffer tsn: 0 rdba: 0x0040ce7a (1/52858)
scn: 0x0000.13e26d0c seq: 0x01 flg: 0x00 tail: 0x6d0c0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0040ce7a
Object id on Block? Y
seg/obj: 0x7c54 csc: 0x00.13e26d0c itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.010.00000177 0x0080183b.0064.1e ---- 1 fsc 0x0000.00000000
0x02 0x0003.004.0000017b 0x00801dd0.0073.11 ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0x665105c
tsiz: 0x1fa0
hsiz: 0x18
pbl: 0x0665105c
bdba: 0x0040ce7a
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f6d
avsp=0x1f55
tosp=0x1f55
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f8f
0x14:pri[1] offs=0x1f7e
0x16:pri[2] offs=0x1f6d
block_row_dump:
Which may or may not tell you a hell of a lot. Yes it does say that I have 2 interested transactions but it isnt the clearest file one has ever seen, nor is it obvious that header_block+1 is the first datablock in a segment.
-- Niall Litchfield Oracle DBA Audit Commission Uk "VC" <boston103_at_hotmail.com> wrote in message news:31e0625e.0310300645.4c908003_at_posting.google.com...Received on Thu Oct 30 2003 - 10:18:13 CST
> Hello Ben,
>
> The behaviour you're observing is explained by Oracle 9i's creating
> _two_ ITL slots instead of one despite your specifying 'initrans 1'.
> It can be easily verified by dumping the data block.
>
> If you perform an update #3 on row #3 in addition to the two you've
> already initiated, you'll get the desired effect.
>
> Rgds.
>
>
> "ben brugman" <ben_at_niethier.nl> wrote in message
news:<3fa0f04d$0$245$4d4ebb8e_at_read.news.nl.uu.net>...
> > At the moment I am playing around with an Oracle database.
> >
> > I have set The number of transactions for a test table
> > initial to 1 and maximum to 1.
> > I enter 3 small rows into a table.
> > (All rows end up in the same block, when selecting with rowid
> > all is the same except that the last digit becomes A or B or C).
> >
> > Now I open a session and do an update on the first row.
> > I open a second session and do an update on the second row.
> >
> > Both updates succeede and I can commit both.
> > Because of the only one transaction in a block I would expect that
> > one transaction would be blocked by the first.
> >
> > What am I missing or understanding wrongly.
> >
> > ben brugman