Oracle table lock issue

Posted on 2011-10-12
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...?

Question by:toooki
    LVL 14

    Assisted Solution

    You can not only trace the user, but also the machine (host) name. osusername

    Check if this script is useful to find other info:


    Author Comment

    Thank you.
    LVL 15

    Accepted Solution

    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;

    Author Comment

    Thank you.
    Yes that seems to be the a user when they queried on dba_users;
    I found the details of the lock.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now