Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle table lock issue

Posted on 2011-10-12
4
Medium Priority
?
726 Views
Last Modified: 2012-08-13
In my Oracle (11gR2) database one procedure fails occasionally at the point when the procedure tries to truncate a database table.

The log shows this:
ErrorCode: -54 ErrorText: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. Table name: myTab1 Session id: 657, Process id: 143213, Databae user: ORACLE1, Locked mode: 2.

The above content is obtained via querying the V$LOCKED_OBJECT table within the procedure exception code.
I know that the failure is because of the table lock on myTab1 . I run the procedure as the user "myUser1". I see that the lock was created by Databae user: ORACLE1 as in the log.

But there does not seem to be an user named ORACLE1 (but I am not sure, I do not have DBA access) in the database...

I am trying to find what to conclude for the log --- how that lock was created on the table. Is there a generic ORACLE1 user or so in Oracle...?
Thanks,

0
Comment
Question by:toooki
  • 2
4 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 948 total points
ID: 36959555
You can not only trace the user, but also the machine (host) name. osusername

Check if this script is useful to find other info:

http://www.oracle-base.com/dba/Script.php?category=10g&file=lock_tree.sql


0
 

Author Comment

by:toooki
ID: 36960249
Thank you.
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 952 total points
ID: 36961285
Hi,
You should check where that log information comes from.
the locks can be seen from V$LOCKED_OBJECT then you should have an 'ORACLE1' user in dba_users;
Regards,
Franck.
0
 

Author Comment

by:toooki
ID: 36966751
Thank you.
Yes that seems to be the a user when they queried on dba_users;
I found the details of the lock.
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question