Rao_S
asked on
How to find the resource causing ORA-00054?
do you have a code that i could use to find which resource is causing a table lock?
i have a packaged procedure which is scheduled to run daily, i get this error ocationally not every day:
-54;ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
i can change the run time of the job to avoid a potential table lock, but i need to find what is causing this error.
i have a packaged procedure which is scheduled to run daily, i get this error ocationally not every day:
-54;ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
i can change the run time of the job to avoid a potential table lock, but i need to find what is causing this error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi markgeer, yes you are correct, the job which is throwing a -54 is a pl/sql procedure which runs at 6am every morning. the job first truncates the table, and then runs a select and inserts into the table.
we think the error is comming from the truncate. we think we know that the possibility of anybody else trying to insert or update that table is negligible.
so we think that the table is lock by the procdure from the previous day's run.
so we need a query which will show us who is holding the lock on the table.
i tried to open a sqlplus window and executed the procedure, it shows me that procedure is waiting the table, i want to see who is holding the table..... any suggestion?
we think the error is comming from the truncate. we think we know that the possibility of anybody else trying to insert or update that table is negligible.
so we think that the table is lock by the procdure from the previous day's run.
so we need a query which will show us who is holding the lock on the table.
i tried to open a sqlplus window and executed the procedure, it shows me that procedure is waiting the table, i want to see who is holding the table..... any suggestion?
ASKER
thank you gs79, i used your first query and it shows me that the procedure is waiting for the table, is there a way to see what is holding the table?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you markgeer,
i opened multiple sessions of sqlplus and caused a lock..i was trying to understand what mode and type mean..i have attached a file, please take a look..
Locks1.xls
i opened multiple sessions of sqlplus and caused a lock..i was trying to understand what mode and type mean..i have attached a file, please take a look..
Locks1.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thnak you!
ASKER