This discussion is archived
12 Replies Latest reply: May 21, 2007 7:31 AM by BluShadow RSS

ALTER TABLE ..... RENAME ..... TO ..... does not work ?

562466 Newbie
Currently Being Moderated
When I enter the following command in SQLplus it is accepted (and executed ?) without problems.

ALTER TABLE tablesum
RENAME currency TO curr;

However when I enter afterwards a

SELECT * FROM tablesum;

then as header of the column appears still "currency"

Does RENAME only affect the column name and NOT its header ?
If yes how can I change both column name and header together ?

Yes, I have done a COMMIT;
  • 1. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    Boneist Guru
    Currently Being Moderated
    I got an error when I tried your command, but when corrected, it appears to work?
    SQL> create table tablesum(currency number);

    Table created.

    SQL>
    SQL> desc tablesum
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    CURRENCY                                           NUMBER

    SQL>
    SQL> alter table tablesum rename currency to curr;
    alter table tablesum rename currency to curr
                                *
    ERROR at line 1:
    ORA-14155: missing PARTITION or SUBPARTITION keyword


    SQL>
    SQL> alter table tablesum rename column currency to curr;

    Table altered.

    SQL>
    SQL> desc tablesum
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    CURR                                               NUMBER
  • 2. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    SQL> desc emp2
    Name                                                                                Null?    Type
    ----------------------------------------------------------------------------------- -------- -------------------
    EMPNO NUMBER(4)
    ENAME                                                                                        VARCHAR2(10)
    JOB                                                                                          VARCHAR2(9)
    MGR                                                                                          NUMBER(4)
    HIREDATE                                                                                     DATE
    SAL                                                                                          NUMBER(7,2)
    COMM                                                                                         NUMBER(7,2)
    DEPTNO                                                                                       NUMBER(2)
    NEW_COL                                                                             NOT NULL NUMBER

    SQL> alter table emp2 rename empno to empid;
    alter table emp2 rename empno to empid
                            *
    ERROR at line 1:
    ORA-14155: missing PARTITION or SUBPARTITION keyword


    SQL> alter table emp2 rename column empno to empid
    SQL> /

    Table altered.

    SQL> select * from emp2;

         EMPID ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO    NEW_COL
    ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17/12/80        800                    20          0
          7499 ALLEN      SALESMAN        7698 20/02/81       1600        300         30          0
          7521 WARD       SALESMAN        7698 22/02/81       1250        500         30          0
          7566 JONES      MANAGER         7839 02/04/81       2975                    20          0
          7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30          0
          7698 BLAKE      MANAGER         7839 01/05/81       2850                    30          0
          7782 CLARK      MANAGER         7839 09/06/81       2450                    10          0
          7788 SCOTT      ANALYST         7566 19/04/87       3000                    20          0
          7839 KING       PRESIDENT            17/11/81       5000                    10          0
          7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30          0
          7876 ADAMS      CLERK           7788 23/05/87       1100                    20          0
          7900 JAMES      CLERK           7698 03/12/81        950                    30          0
          7902 FORD       ANALYST         7566 03/12/81       3000                    20          0
          7934 MILLER     CLERK           7782 23/01/82       1300                    10          0

    14 rows selected.

    SQL>
    Nicolas.
  • 3. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    user132933 Newbie
    Currently Being Moderated
    ALTER TABLE tablesum
    RENAME column currency TO cur;

    regards
  • 4. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    388131 Explorer
    Currently Being Moderated
    COMMIT is irrelevant in this case.
    What's your db version? Can you post the original statement and the sqlplus output of it?
  • 5. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    3520 Explorer
    Currently Being Moderated
    Yes, I have done a COMMIT;
    It is DDL (Data definition language) command, with implicit commit BTW. So you don't need it (and cannot rollback as well if executed succesfully).

    Gints Plivna
    http://www.gplivna.eu
  • 6. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    and cannot rollback as well if executed succesfully
    Not only if succesfull. Even if not succesfully, in some cases.
    Here below, two examples.
    First is a syntax error after which you can rollback, but the second example is an other error (duplicate name) after which you cannot rolling back transaction.
    SQL> insert into emp2 select * from emp;

    14 rows created.

    SQL> commit;

    Commit complete.

    SQL> select * from emp2;

         EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17/12/80        800                    20
          7499 ALLEN      SALESMAN        7698 20/02/81       1600        300         30
          7521 WARD       SALESMAN        7698 22/02/81       1250        500         30
          7566 JONES      MANAGER         7839 02/04/81       2975                    20
          7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01/05/81       2850                    30
          7782 CLARK      MANAGER         7839 09/06/81       2450                    10
          7788 SCOTT      ANALYST         7566 19/04/87       3000                    20
          7839 KING       PRESIDENT            17/11/81       5000                    10
          7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30
          7876 ADAMS      CLERK           7788 23/05/87       1100                    20
          7900 JAMES      CLERK           7698 03/12/81        950                    30
          7902 FORD       ANALYST         7566 03/12/81       3000                    20
          7934 MILLER     CLERK           7782 23/01/82       1300                    10

    14 rows selected.

    SQL> delete emp2;

    14 rows deleted.

    SQL> alter table emp2 rename empno to empid;
    alter table emp2 rename empno to empid
                            *
    ERROR at line 1:
    ORA-14155: missing PARTITION or SUBPARTITION keyword


    SQL> rollback;

    Rollback complete.

    SQL> select * from emp2;

         EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17/12/80        800                    20
          7499 ALLEN      SALESMAN        7698 20/02/81       1600        300         30
          7521 WARD       SALESMAN        7698 22/02/81       1250        500         30
          7566 JONES      MANAGER         7839 02/04/81       2975                    20
          7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01/05/81       2850                    30
          7782 CLARK      MANAGER         7839 09/06/81       2450                    10
          7788 SCOTT      ANALYST         7566 19/04/87       3000                    20
          7839 KING       PRESIDENT            17/11/81       5000                    10
          7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30
          7876 ADAMS      CLERK           7788 23/05/87       1100                    20
          7900 JAMES      CLERK           7698 03/12/81        950                    30
          7902 FORD       ANALYST         7566 03/12/81       3000                    20
          7934 MILLER     CLERK           7782 23/01/82       1300                    10

    14 rows selected.

    SQL> delete emp2;

    14 rows deleted.

    SQL> alter table emp2 rename column empid to empno;
    alter table emp2 rename column empid to empno
    *
    ERROR at line 1:
    ORA-00957: duplicate column name


    SQL> rollback;

    Rollback complete.

    SQL> select * from emp2;

    no rows selected

    SQL>
    Nicolas.
  • 7. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    562466 Newbie
    Currently Being Moderated
    Hmm, sorry to say but it still doesn't work (even with COLUMN keyword.

    First of all I am surprised that SQLplus accepts an
    SQL command with a missing keyword "COLUMN". I would have expected an error message like "Missing COLUMN" or something else.

    Furthermore the column header is still displayed as the old column header before.

    I could imagine that the reason is a not flushed buffer: i am accessing the table in a SQL script just before (the rename command). Could it be that SQLplus tries to "optimize" the access and lists the table which it holds in the cache?

    Is there something like a "flush buffer" command (beside COMMIT) ?
  • 8. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    3520 Explorer
    Currently Being Moderated
    and cannot rollback as well if executed
    succesfully
    Not only if succesfull. Even if not succesfully, in
    some cases.
    Here below, two examples.
    First is a syntax error after which you can rollback,
    but the second example is an other error (duplicate
    name) after which you cannot rolling back
    transaction.
    SQL> alter table emp2 rename empno to empid;
    alter table emp2 rename empno to empid
    *
    155: missing PARTITION or SUBPARTITION keyword
    Yea because this to ORCALE is the same command as for example
    zyriohjdfas jhgkjhg jhgjhadgsj;
    :)

    OK let's formulate it as
    and cannot rollback as well if issued syntactically valid DDL statement
    because as you know Oracle converts it to
    commit;
    DDL;
    commit;

    I wonder is it (cannot rollback if issued syntactically valid DDL statement) the precise definition?

    Gints Plivna
    http://www.gplivna.eu
  • 9. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    388131 Explorer
    Currently Being Moderated
    Again: What's your db version? Can you post the original statement that has been issued as well as its output shown in the sqlplus session?
  • 10. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    544878 Newbie
    Currently Being Moderated
    SQL> desc emp
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    EMP_ID NUMBER(3)
    NAME VARCHAR2(20)
    DEPT VARCHAR2(10)


    SQL> alter table emp rename column emp_id to id;

    Table altered.

    SQL> desc emp
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ID NUMBER(3)
    NAME VARCHAR2(20)
    DEPT VARCHAR2(10)

    SQL>

    more information please let me know.
  • 11. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    zyriohjdfas jhgkjhg jhgjhadgsj;
    There is an error, this is : zyriohjdfase jhgkjhg jhgjhadgsj;
    ;-)
    and cannot rollback as well if issued syntactically valid DDL statement
    Nice correction !

    Best regards,

    Nicolas.
  • 12. Re: ALTER TABLE ..... RENAME ..... TO ..... does not work ?
    BluShadow Guru Moderator
    Currently Being Moderated
    Hmm, sorry to say but it still doesn't work (even
    with COLUMN keyword.

    First of all I am surprised that SQLplus accepts an
    SQL command with a missing keyword "COLUMN".
    That's because there is an ALTER TABLE <table> RENAME [<partition>|<subpartition] ..... command which is valid.
    I would have expected an error message like "Missing COLUMN"
    or something else.
    But, there is an error message. It tells you effectively that there is no partition or sub partition i.e. you are missing the partition/sub-partition keyword.
    Furthermore the column header is still displayed as
    the old column header before.
    No it's not as everyone has demonstrated.
    I could imagine that the reason is a not flushed
    buffer: i am accessing the table in a SQL script just
    before (the rename command). Could it be that SQLplus
    tries to "optimize" the access and lists the table
    which it holds in the cache?
    Is there something like a "flush buffer" command
    (beside COMMIT) ?
    No, it's not a buffer issue.

    How about copying and pasting exactly what you are getting in your window.