5
RENAME COLUMN table-Name.simple-Column-Name TO simple-Column-Name

When i try to rename column i am getting following error message on all columns:

RENAME COLUMN EMP_NEW.EMPLOYEE_ID TO EMPLOYEEID
Error report -
ORA-00903: invalid table name
00903. 00000 -  "invalid table name"
*Cause:    
*Action:

Following is version

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
"CORE   12.1.0.1.0  Production"
TNS for IBM/AIX RISC System/6000: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

Any suggestion how to resolve this issue? (Note i have created structure from some existing table and not copied anything i.e no index, no constraint nothing, so it is simple create)

fatherazrael
  • 5,511
  • 16
  • 71
  • 155

2 Answers2

7

Correct Syntax is

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Use following. but ensure EMP_NEW table is present in the schema you have used to logon

ALTER TABLE EMP_NEW RENAME COLUMN EMPLOYEE_ID TO EMPLOYEEID;
Gro
  • 1,613
  • 1
  • 13
  • 19
1

EDIT

Don't use this syntax, this is derby syntax, use @Gro answer (alter table)

Make sure table not in different scheme than user, or use scheme name.

Also make sure there aren't any open cursors that reference the column

Restriction: The RENAME COLUMN statement is not allowed if there are any open cursors that reference the column that is being altered.

Community
  • 1
  • 1
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • i am able to do it using alter but not rename :(. Even i dropped table and created again – fatherazrael Jul 23 '19 at 05:26
  • @fatherazrael alter table is the standard way to *alter* table – Ori Marko Jul 23 '19 at 05:30
  • 1
    @fatherazrael - As [the Oracle SQL reference states](https://docs.oracle.com/database/121/SQLRF/statements_9021.htm#SQLRF01608): *"Use the RENAME statement to rename a table, view, sequence, or private synonym"* So not column. – APC Jul 23 '19 at 06:19
  • @APC so statement valid until Oracle 10g only? – Ori Marko Jul 23 '19 at 06:20
  • 1
    Nope. Not RENAME A TO B did not work with columns even in 10g. From [the 10g SQL ref](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9019.htm#i2064929); *"You cannot use this statement directly to rename columns. However, you can rename a column using the ALTER TABLE ... rename_column_clause. "* – APC Jul 23 '19 at 06:24