Help: Problems with ALTER TABLE

[Maximilian Scherf]

Hello,

I have two problems with the ALTER TABLE command (Warning: I'm not
exactly an Oracle expert):

First Problem:

I want to change the name of a column. I've tried the following:
ALTER TABLE <table_nameRENA ME COLUMN <old_column_nam eTO
<new_column_nam e>;
The error I get is "ORA-14155: Keyword PARTITION or SUBPARTITION is
missing." (That's translated from german, so the text probably isn't
perfectly accurate).
I have no idea what that means.

Second Problem:

I want to (re)enable a foreign key constraint. My try:
ALTER TABLE <tablenameENABL E CONSTRAINT <constraint_nam e>;
Error: "ORA-00054: Tried to access an occupied resource with NOWAIT."
(same as above).
I've also tried locking the table but I get the same error then.

My Oracle Version is 8.1.7, I'm on a Win2000 client, the server is
some fat HPUX box (also running 8.1.7).

Any help is appreciated. Thanks in advance and best regards,

Max

[Steffen Krippner]

Re: Help: Problems with ALTER TABLE

Hi,
here we comes the Oracle description for this error code:

ORA-00054 resource busy and acquire with NOWAIT specified

Cause: The NOWAIT keyword forced a return to the command prompt
because a resource was unavailable for a LOCK TABLE or SELECT FOR
UPDATE command.

Action: Try the command after a few minutes or enter the command
without the NOWAIT keyword.

Thank you,
Steffen

[Steffen Krippner]

Re: Help: Problems with ALTER TABLE

Hi,
thanks for helping- but I still do not get it. I do the "lock table"
in the first session. After that the updates in the second session
stop. That is fine since the first session got the table lock. When I
do the "Alter Table" in the first session it fails with the 00054
error "resource busy". Shouldn't this succeed since I hold the table
lock already in this session?

Thanks,
Steffen

[sybrandb@yahoo.com]

Re: Help: Problems with ALTER TABLE

steffenkrippner @yahoo.com (Steffen Krippner) wrote in message news:<8a219c99. 0307202211.766b 1f8a@posting.go ogle.com>...

Hi,
thanks for helping- but I still do not get it. I do the "lock table"
in the first session. After that the updates in the second session
stop. That is fine since the first session got the table lock. When I
do the "Alter Table" in the first session it fails with the 00054
error "resource busy". Shouldn't this succeed since I hold the table
lock already in this session?
>
Thanks,
Steffen

No
The situation you have is that your affected table is already in use
prior to your ALTER TABLE command. Hence the *table definition* is
locked, and you won't be capable to get another lock. You need to make
sure no one has the table in use. The V$ACCESS view shows which
sessions are using it.

Sybrand Bakker
Senior Oracle DBA

[Steffen Krippner]

Re: Help: Problems with ALTER TABLE

Thanks.
I am still wondering that Oracle forces everybody to stop using a
table before some "schema evolution" is possible.
Is there any other way to do the "Alter Table" perhaps by specifying
the command should wait for the lock instead of aborting with
ORA-00054???