What went wrong with this rename column statement?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: What went wrong with this rename column statement?

Hybrid View

  1. #1
    Join Date
    Oct 2003
    Posts
    6

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    6
    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

  3. #3
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Correct syntax is :

    ALTER TABLE -table_name- RENAME COLUMN -old_name- TO -new_name-;
    -- Dilip

  4. #4
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    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.

  5. #5
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Rename column is new to Oracle 9i. This error you would get in Oracle 8i.
    -- Dilip

  6. #6
    Join Date
    Oct 2003
    Posts
    6
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Oct 2003
    Posts
    6
    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.

  9. #9
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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
  •  


Click Here to Expand Forum to Full Width