By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,322 Members | 1,182 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,322 IT Pros & Developers. It's quick & easy.

Help: Problems with ALTER TABLE

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
Jul 19 '05 #2

P: n/a

"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.
Jul 19 '05 #3

P: n/a
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
Jul 19 '05 #4

P: n/a
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
Jul 19 '05 #5

P: n/a
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
Jul 19 '05 #6

P: n/a
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???
Jul 19 '05 #7

P: 1
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.
Sep 5 '05 #8

P: 23
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]
Sep 5 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.