Changing Oracle table name

Antarr Byrd picture Antarr Byrd · Nov 8, 2012 · Viewed 29.4k times · Source

I'm trying to change a table name in oracle. I first run this script to declare the table

CREATE TABLE CUSTOMER
(
  C_ID NUMBER(6,0),
  C_LAST VARCHAR2(25),
  C_FIRST varchar2(25),
  C_MI char(1),
  C_DOB DATE,
  C_ADDRESS varchar2(100),
  C_CITY varchar2(35),
  C_STATE char(2),
  C_ZIP varchar2(10),
  C_DPHONE varchar2(12),
  C_EPHONE varchar2(12),
  C_USERID varchar2(20),
  C_PASSWORD VARCHAR2(30),
  CONSTRAINT CUSTOMER_PK PRIMARY KEY(C_ID)
);

CREATE TABLE ORDER_SOURCE
(
  OS_ID NUMBER(6),
  OS_DESC varchar2(255),
  CONSTRAINT ORDER_SOURCE_PK PRIMARY KEY(OS_ID)
);

CREATE TABLE ORDERS
(
  O_ID NUMBER(6),
  O_DATE DATE,
  O_METHPMT varchar2(25),
  C_ID NUMBER(6),
  OS_ID NUMBER(6),
  CONSTRAINT ORDERS_PK PRIMARY KEY(O_ID),
  CONSTRAINT ORDERS_CUSTOMER_FK FOREIGN KEY(C_ID) REFERENCES CUSTOMER(C_ID),
  CONSTRAINT ORDERS_ORDER_SOURCE_FK FOREIGN KEY(OS_ID) REFERENCES ORDER_SOURCE(OS_ID)
);

It runs correctly, I then try to run

alter table ORDERS
rename to ORDER;

I get this error:

Error starting at line 1 in command: alter table ORDERS rename to ORDER Error report: SQL Error: ORA-00903: invalid table name 00903. 00000 - "invalid table name" *Cause:
*Action:

Answer

Vladimir picture Vladimir · Jul 24, 2013

Syntax "RENAME TABLE tab_old TO tab_new" is not correct one.
Correct syntax: "RENAME tab_old TO tab_new".
Word "TABLE" shouldn't be in the statement.