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_name> RENAME COLUMN <old_column_name> TO
<new_column_name>;
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 <tablename> ENABLE CONSTRAINT <constraint_name>;
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]

Hi,
the problem I am facing is a little different:

Session A is doing updates on the table.
In Session B (AUTOCOMMIT OFF) I issue the following commands:
1) lock table t in exclusive mode;
2) alter table t add (xxx number(10,0));

Result:
After (1) the updates off session A come to a halt. (Obviously fine)
After (2) I get ORA-00054.

Any explanations?

Thanks.
Steffen

[Alan Mills]

"Steffen Krippner" <st*************@yahoo.com> wrote in message
news:8a**************************@posting.google.c om...

Hi,
the problem I am facing is a little different:

Session A is doing updates on the table.
In Session B (AUTOCOMMIT OFF) I issue the following commands:
1) lock table t in exclusive mode;
2) alter table t add (xxx number(10,0));

Result:
After (1) the updates off session A come to a halt. (Obviously fine)
After (2) I get ORA-00054.

Not having memorised every oracle error code. remind me what error 54 is
please.

[Steffen Krippner]

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]

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]

st*************@yahoo.com (Steffen Krippner) wrote in message news:<8a**************************@posting.google. 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]

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???

[poupe]

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_name> RENAME COLUMN <old_column_name> TO
<new_column_name>;
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 <tablename> ENABLE CONSTRAINT <constraint_name>;
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

Here is your answer:
As refered to Oracle products.
You must have at least Oracle 9i Release 2
that can solve your problems.

From Lao PDR.

[richasaraf]

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

------
[FONT=Palatino Linotype][COLOR=Red]hey... even i use to face this problem.....
this error occurs when from apps u are inserting or transacting with one table and while that is going on you acces that table from back end maybe from SQL Navigator or so...
the solution to this is .... which i did... i asked my DBA to unlock the table or kill the session of apps and the error goes away...... :)

and it really helped me......
and for the ALTER TABLE .... got o following link... this helped me when i was learning.....

http://www.techonthenet.com/sql/tables/alter_table.php

This site gives you the basic syntax of ALTER TABLE ... and has many more... so you can really learn a lot !


Thanks a lot ! :)
Richa[/COLOR][/FONT]