Wich parameter could cause this lock behaviour?

Hi there!

Some time ago one of our customers upgraded their oracle-server from 8.1.7.3 to 9.2.0.7. After this upgrade i recieved some calls from them about strange behauviour when trying to run som upgrades on our application. I traced it down to be a 'CREATE INDEX' Statement (on an index that already existed) that was blocked (waiting for a release of a row-exclusive lock on a table). This would propably (to my knowledge, after reading the books...) be quite expected. The confusing part for me is that the row-exclusive lock is due to an 'SELECT FOR UPDATE'. As the same upgrade has been run (sucessfully) on quite a few servers i started to dig into this.

I checked their parameters and then set up a system with the same parameters on a test-server to see if i can understand what parameters are involved. I have compared the v$parameter views and the only parameters that differs now
(after changin a few on the test-server) are regarding memory, size and file
destinations.

I still got the difference when running quite simple statements.
(i executed them from sql-plus running on the servers to rule out the
application and net8-stuff):

Both tests below are done with only one user connected. (the sql-plus session)

1. ) My test-server (Win 2003), also tried on some other servers which had clean 9.2 installs (not upgraded from 8.1)

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

drop table test;
create table test(n number(2));
insert into test values(1);
commit;
select * from test where n = 1 for update nowait;

select type, lmode, decode(lmode,2,'row-share',3,'row-exclusive'),

     decode(type,'TM',(select object_name from dba_objects where
     object_id=id1)) name from v$lock
     where sid = (select sid from v$mystat where rownum=1);

this results in:

TY LMODE DECODE(LMODE, NAME
-- ---------- ------------- --------

TX          6
TM          2 row-share     TEST

2. )
The other server (Win 2000):

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

Same sql as above

results in:

 TY LMODE DECODE(LMODE, NAME

-- ---------- ------------- ----------------
TX          6
TM          3 row-exclusive TEST

Why do i get a row exclusive on this?

I have searched in the documentation, metalink and the internet for info regarding this, but it seems that
1.Either this is so obvius that no one has seen the need to describe how this could be.
 Or
2. I am looking for the wrong thing. (searched for the wrong keywords)

I realize that in both cases it would be me who is not 'oracle-educated' enough... But i would really appreciate if someone could point me in the right directions regarding this. Where to look after parameters which could cause this (wanted?) behaviour. I have checked some reading about parallel server, replication and standby-stuff but i can not se the evidence that something like that is in use on the particular server.

Feel fre to ask for more information if it is needed.

TIA. Best Regards

Tobias Rangne Received on Mon Jan 16 2006 - 10:43:16 CST