ORA-54 although no locks on table

Posted on 2005-05-11
Last Modified: 2008-01-09
I have imported a user into a new oracle database on Linux. When I try to update a table, I get ora-54 error although there are no locks reported on that table. I have verifed this by querying v$lock view. Is this an oracle bug? Need resolution asap. Thanks!
Question by:chundi_gus

    Expert Comment

    query v$access to see which session is using the object. Clear that session and try again.
    LVL 25

    Expert Comment

    What's the PCTFREE on the table?  If it's ZERO, that's most likely the problem.

    Read this:

    ITL Entry Shortages
    There is an interested transaction list (ITL) in the variable header of each Oracle data block. When a new block is formatted for a segment, the initial number of entries in the ITL is set by the INITRANS parameter for the segment. Free space permitting, the ITL can grow dynamically if required, up to the limit imposed by the database block size, or the MAXTRANS parameter for the segment, whichever is less.

    Every transaction that modifies a data block must record its transaction identifier and the rollback segment address for its changes to that block in an ITL entry. (However, for discrete transactions, there is no rollback segment address for the changes.) Oracle searches the ITL for a reusable or free entry. If all the entries in the ITL are occupied by uncommitted transactions, then a new entry will be dynamically created, if possible.

    If the block does not have enough internal free space (24 bytes) to dynamically create an additional ITL entry, then the transaction must wait for a transaction using one of the existing ITL entries to either commit or roll back.
    So, even if there isn't a lock, it's possible that you can't get an ITL entry.

    Try this query:

    select owner,
           object_name||' '||subobject_name object_name,
      from v$segment_statistics
     where statistic_name = 'ITL waits'
     and value > 0
    order by 3,1,2;

    Any results?
    LVL 6

    Expert Comment

    Is this a single user database?  If not, since you just imported this schema, see if there any foreign key constraints on tables in this new schema?  If so, do you have indexes built on those columns?  If not, you may be getting locked out of a table you other than the one you think you are.  In most cases, you want indexes on the column where the constraint is defined to avoid errors such as this one.

    Author Comment

    V$access shows no locks on that table and the ITL waits query retuns no rows. The PCT_FREE is 10, INI_TRANS is 1, MAX_TRANS is 255 and PCT_INCREASE is null on that table. Any clue?

    Author Comment

    There are foreign key constraints on columns and the columns are indexed. Any ideas on what else could be causing this?
    LVL 6

    Expert Comment

    Again, are you the only person using this database?  Are the constraints 'status' and 'validated' equal to 'ENABLED' and 'VALIDATED'?
    select object_id from dba_objects where object_name='<tablename>';
    select * from v$locked_object where object_id=<id number);
    is the object in fact locked?  does that session need to have it locked?

    Author Comment

    I found the solution. There is a trigger on that table that fires before update and insert. I disabled that trigger and then I was able to update the table without a problem. Thanks for all your responses!
    LVL 5

    Expert Comment


    You need to rebuild your table with a slightly bigger PCTFREE allowance to avoid future occurrences of this error.
    We are encountering ORA-54 errors when we clearly know that the said rows are not locked by any other user process. This happens when we do SELECT FOR UPDATE NOWAIT. We get hundreds of these on a single day. When we re-try after a second, we succeed for 90 percent of them but still fail for some. Is there any other reason (other than the row being locked by another process) to receive this error

    LVL 1

    Accepted Solution

    PAQed with no points refunded (of 250)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 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

    11 Experts available now in Live!

    Get 1:1 Help Now