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

Martin T.

unread,

Sep 21, 2006, 12:34:02 PM9/21/06

to

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

gazzag

unread,

Sep 21, 2006, 12:43:33 PM9/21/06

to

Are you sure that there are no other background processes, for example,
logged into the database?

What does gv$session show you?

Martin T.

unread,

Sep 21, 2006, 12:52:09 PM9/21/06

to

Yes, I'm 100% sure no other session that the one opened by the script
are active. We are only two developers on this test DB. Also the schema
the script runs against is freshly created from an export dump. No jobs
run that access this schema.

Can it be that an uncommited DML in a earlier part of the script locks
something? (I thought to know that ALTER implies commit though.)

best,
Martin

DA Morgan

unread,

Sep 21, 2006, 2:35:03 PM9/21/06

to

Build another table with the same DDL but a different name.
Try your script on it.

Assuming what you've said I'd be thinking corrupt block/segment or a
lock that was not properly released.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Frank van Bortel

unread,

Sep 21, 2006, 3:17:27 PM9/21/06

to

Martin T. schreef:

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"...

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Martin T.

unread,

Sep 22, 2006, 4:39:14 AM9/22/06

to

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