CODEWITHSUNDEEP
sqloracle11galter-tabletable-rename
Antarr Byrd
Antarr Byrd

Reputation: 26071

Changing Oracle table name

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:

Upvotes: 6

Views: 33652

Answers (3)

Vladimir
Vladimir

Reputation: 441

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.

Upvotes: 44

Shepherdess
Shepherdess

Reputation: 651

order is a reserved word in oracle so you cannot use it as a table name. You can try escaping with double quotes ("order") but it's not a good practice

Upvotes: 5

djadmin
djadmin

Reputation: 1760

RENAME TABLE table-Name TO new-Table-Name

If there is a view or foreign key that references the table, attempts to rename it will generate an error. In addition, if there are any check constraints or triggers on the table, attempts to rename it will also generate an error.

And in your case, the "ORDER" table-name is RESERVED so please try to change the name

Upvotes: 0

Related Questions