-
What went wrong with this rename column statement?
What went wrong with this rename column statement?
SQL> ALTER TABLE testrename RENAME PROCESSED to PROCESSED_new;
ALTER TABLE testrename RENAME PROCESSED to PROCESSED_new
*
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
SQL> desc testrename
Name Null? Type
----------------------------------------- -------- ----------------------------
SKN NUMBER
ASIN VARCHAR2(10)
UPC VARCHAR2(20)
PRODUCT_NAME VARCHAR2(255)
COPY VARCHAR2(2000)
AGE_FROM VARCHAR2(25)
AGE_TO VARCHAR2(25)
BATTERY_INCLUDED VARCHAR2(3)
BATTERY_TYPE VARCHAR2(20)
BATTERY_AMOUNT VARCHAR2(20)
PROCESSED VARCHAR2(1)
I am sure the syntex is correct but I kept on getting the same messgage from three databases on 3 versions: 8.1.6, 8.1.7 and 9.2.0.
I have renamed columns before and don't remember what I did differently. Could anyone figure out what is the problem here?
Thanks.
-
Correction to above, this is the statement but still failed:
SQL> ALTER TABLE testrename RENAME COLUMN PROCESSED to PROCESSED_new;
ALTER TABLE testrename RENAME COLUMN PROCESSED to PROCESSED_new
*
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
-
Correct syntax is :
ALTER TABLE -table_name- RENAME COLUMN -old_name- TO -new_name-;
-- Dilip
-
SQL> desc test
Name Null? Type
----------------------------------------- -------- ------------------
B NUMBER(38)
SQL> alter table test rename column b to a;
Table altered.
SQL> desc test
Name Null? Type
----------------------------------------- -------- -----------------
A NUMBER(38)
I suspect you have partitioned table based on the errors messages you provided.
-
Rename column is new to Oracle 9i. This error you would get in Oracle 8i.
-- Dilip
-
Just did the test in the 9i database still got the same thing. No way this test table is partitioned. Also I know it works in 8i because I have done it before:
SQL> create table test1 (b number(38));
Table created.
SQL> desc test1;
Name Null? Type
----------------------------------------- -------- -------------------------
B NUMBER(38)
SQL> alter table test rename column b to a;
alter table test rename column b to a
*
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
-
What is your COMPATIBLE init parameter in your 9i database set to? I bet it's '8.1.0' (the default). It should be set to at least '9.0.0' to enable you renaming the columns.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
No. In the db the parameter is
SQL> sho parameter compatible
NAME_COL_PLUS_SHOW_PARAM TYPE
---------------------------------------------------------------- -------
VALUE_COL_PLUS_SHOW_PARAM
----------------------------------------------------------------------------
compatible string
9.0.0
BUT I tested one more time in a 9.2.0 db and it finally works. So I guess nothing wrong with the syntex just the db has to be set properly. I really don't think rename only works in 9.2.0 but that is what it seems for now..
SQL> sho parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0
SQL> alter table test1 rename column b to a;
Table altered.
-
Originally posted by tsaoj
Just did the test in the 9i database still got the same thing. No way this test table is partitioned. Also I know it works in 8i because I have done it before:
rename wont work in 8i. what version of oracle 9i are you using? I thought this was oracle 9iR2 feature.
-- Dilip
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|