ORA-54 although no locks on table

I have imported a user into a new oracle 9.2.0.3 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!
chundi_gusAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DarthModConnect With a Mentor Commented:
PAQed with no points refunded (of 250)

DarthMod
Community Support Moderator
0
 
sharmanarendraCommented:
query v$access to see which session is using the object. Clear that session and try again.
0
 
jrb1Commented:
What's the PCTFREE on the table?  If it's ZERO, that's most likely the problem.

Read this:
---------
http://www.akadia.com/services/ora_locks_survival_guide.html

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,
       value
  from v$segment_statistics
 where statistic_name = 'ITL waits'
 and value > 0
order by 3,1,2;

Any results?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jwittenmCommented:
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.
0
 
chundi_gusAuthor Commented:
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?
0
 
chundi_gusAuthor Commented:
There are foreign key constraints on columns and the columns are indexed. Any ideas on what else could be causing this?
0
 
jwittenmCommented:
Again, are you the only person using this database?  Are the constraints 'status' and 'validated' equal to 'ENABLED' and 'VALIDATED'?
second,
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?
0
 
chundi_gusAuthor Commented:
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!
0
 
helpneedCommented:
hi

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

regards
 
0
All Courses

From novice to tech pro — start learning today.