OraFAQ Forum: Server Administration » random locks in oracle

Home » RDBMS Server » Server Administration » random locks in oracle - no fixed pattern

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

random locks in oracle - no fixed pattern [message #164135]

Tue, 21 March 2006 23:31

sonali_ip
Messages: 3
Registered: March 2006

Junior Member

hi
i am facing a peculiar problem. We at offshore have sent a Pro*C file with 4 queries. Here they are running fine but at onsite they at times produde locks (temporary). The queries are as follows -

1/ Insert rows (via select command) in Global temp table 1
2/ Insert rows (via select command) in Global temp table 2
3/ Delete rows from table 3 where (a condn involving table 3 and the above temp tables)
4/ insert in table 3 (select involving some static data + data from temp table + another view)

Now the lock happens in step 3 always.
pls note that table 3 is a huge table with lots of data and at onsite trace is also ON.
Only if the above 4 statements are successful that finally the pro*c is committed.
I read it somewhere that a select in a update or delete places locks but then my question is why is oracle placing locks in a random manner. Also does having TRACE utility on while some heavy transactions are happening - does this lead to a temp lock?
Also would it help if i added that in the delete and insert query we are also using a "VIEW" (of multiple tables) which is on another database schema.
PLease let me know if some more info is required
Thanking in advance.

Report message to a moderator

Re: random locks in oracle - no fixed pattern [message #164139 is a reply to message #164135]

Tue, 21 March 2006 23:58

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

--> why is oracle placing locks in a random manner?

The best way to know this is to dig into the session trace files.

--> Also does having TRACE utility on while some heavy transactions are happening - does this lead to a temp lock?

No , i do not think so. TRACE is a separate activity.

Report message to a moderator

Re: random locks in oracle - no fixed pattern [message #164148 is a reply to message #164139]

Wed, 22 March 2006 00:18

sonali_ip
Messages: 3
Registered: March 2006

Junior Member

thanks for the reply!
i will ask for the session trace file and then analyse...
Will again post a message if something is strange in the trace file.

Report message to a moderator

Previous Topic:	Oracle 8i DB fragmentation
Next Topic:	Stand By database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 13:44:58 CDT 2024