Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

like image 541
Antarr Byrd Avatar asked Nov 08 '12 15:11

Antarr Byrd


People also ask

How do I rename an existing table?

ALTER TABLE table_name RENAME TO new_table_name; Columns can be also be given new name with the use of ALTER TABLE.

How do I change a table name in SQL?

In Object Explorer, right-click the table you want to rename and choose Design from the shortcut menu. From the View menu, choose Properties. In the field for the Name value in the Properties window, type a new name for the table.

What happens to indexes when you rename a table in Oracle?

When you rename a table, Oracle automatically transfers indexes, constraints, and grants on the old table to the new one. In addition, it invalidates all objects that depend on the renamed table such as views, stored procedures, function, and synonyms.


1 Answers

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.

like image 133
Vladimir Avatar answered Sep 21 '22 02:09

Vladimir