Re: Alter Table and ORA-00054 (only one connection)?

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 22 Sep 2006 01:39:14 -0700
Message-ID: <1158914354.368693.146970@m73g2000cwd.googlegroups.com>

Frank van Bortel wrote:
> Martin T. schreef:
> > Hi all!
> > (Oracle 9.2.0.1.0, Windows XP)
> >
> > I have an update script for a schema that changes a number of tables,
> > does a few insert, updates package specs etc.
> >
> > When I run this sql script on my test DB against the schema (no other
> > connections under the same schema user) for *some* column-adds I get:
> > ALTER TABLE SHIFT_ORDERS ADD (SYS_ORDER_TIME NUMBER)
> > *
> > ORA-00054: resource busy and acquire with NOWAIT specified
> >
> > For some other table alterations the script works.
> >
> > When I run the ALTER commands separately, they always succeed ... ?
> >
> > I guess for some strange reason I'm locking myself, but how can I
> > determine the cause of the lock? (How could I make use of v$access to
> > determine the cause of this?)
> >
> > Any pointers welcome!
> >
> > best,
> > Martin
> >
> Any defaults on the new columns?
> Any triggers (journaling tables!!!) firing?
>
> Especially a combination of the two above can create
> unpleasant side effects on the performance of "a few
> DDL statements"...
>

I have now found out what caused the problem:

Yes, there were triggers on the table that I did not disable at first. However, I then tried to disable the triggers prior to modifying the table, and I then got the ORA-00054 on the <alter table disable all triggers> statement!

I have narrowed down the script to the following:

Session 1.) DROP and recreate the Schema/User
Session 2.) IMPort the user from an export dump
Session 3.) Remove all jobs that were imported with the dump.
Session 4.) Connect as the user

4.1) Run the table modification script.

When I run the script as described here, I get the ORA-00054 in step 4.1 for the very first alter table stmt. However, when I wait a few minutes btw. 3. and 4. then the alter table statements run just fine.
I added a <lock in exclusive mode> prior to the alter table statement, which does the waiting on the work left over from the import ...

The reason for the lock was that a deleted job was still ROLLING BACK when the alter table stmts were run. Argl! :-) You would think they be gone when I delete them and do a commit.

Thanks for all your pointers!

cheers,
Martin Received on Fri Sep 22 2006 - 03:39:14 CDT