-
Locking
In one of our boxes a particular Update statement get locked up frequently and we usually kill the session. How do we go about resolving this.
Thanks
Success Consists of Getting Up Just One More Time Than You've Fallen Down
Be Blessed
-
if you don't mind can i know,
how you identify which update statement gets locked up.
thank you
-Raja
-
by using
select a.sid, a.serial#, a.sql_hash_value, substr(c.object_name,1,25) from v$session a, v$locked_object b, dba_objects c where a.sid = b.sessio
n_id and b.object_id=c.object_id
then use the hash value in
select sql_text,
USERS_OPENING,
EXECUTIONS,
USERS_EXECUTING,
LOADS,
FIRST_LOAD_TIME,
INVALIDATIONS,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS
from v$sqlarea where hash_value=upper('&hash_value')
You can combine bothe quieries to directly fetch teh sql if you want to
Success Consists of Getting Up Just One More Time Than You've Fallen Down
Be Blessed
-
Originally posted by Rohit
by using
select a.sid, a.serial#, a.sql_hash_value, substr(c.object_name,1,25) from v$session a, v$locked_object b, dba_objects c where a.sid = b.sessio
n_id and b.object_id=c.object_id
then use the hash value in
select sql_text,
USERS_OPENING,
EXECUTIONS,
USERS_EXECUTING,
LOADS,
FIRST_LOAD_TIME,
INVALIDATIONS,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS
from v$sqlarea where hash_value=upper('&hash_value')
You can combine bothe quieries to directly fetch teh sql if you want to
I suppose u know what u r talking abt???
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
-
Have you worked out why this operation is so prone to getting locked out?
-
Its from an Oracle Apps Application. We regularly Kill this session whihc runs this update and gets locked up. Can we resolve it without killing the session? Or should any modifiaction be done to the application? Pls do advice..
Success Consists of Getting Up Just One More Time Than You've Fallen Down
Be Blessed
-
if u know a bit abt locking then, u will understand wass the difference in Locking & Blocking.. I hope u really dont mean "When Update Statement Is Run, Table/Object Involved Is Locked Up (ROWS Invloved)"
Abhay.
Last edited by abhaysk; 03-18-2004 at 03:02 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
if u know a bit abt locking then, u will understand wass the difference in Locking & Blocking.. I hope u really dont mean "When Update Statement Is Run, Table/Object Involved Is Locked Up (ROWS Invloved"
Good catch!
Another simple way to see if that process is blocking anyone else would be to use OEM's lock manager. People might hurt thier fingers typing out that much code Rohit
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
Originally posted by OracleDoc
People might hurt thier fingers typing out that much code Rohit
That code is simply useless, what do u think that code tells .. blocking session?? -- No way..
It jus tells u what objects are locked due any DML & the SQL Statement involved..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|