Oracle RENAME Table Example

Oracle RENAME Table Example | The RENAME command of the Oracle database is used for renaming the database objects like tables, and constraints. To rename a table in the Oracle database we must use the RENAME command.

Syntax to rename a table:-

RENAME oldtablename TO newtablename;

Or,

ALTER TABLE oldtablename RENAME TO newtablename;

If the table is owned by another schema,

ALTER TABLE owner.oldtablename RENAME TO newtablename;

When we 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, functions, and synonyms.

Example of Rename Oracle Table

Let us first create a table in the Oracle database to demonstrate renaming an Oracle table.

CREATE TABLE person
(
  id NUMBER(10)
);

Describe the table,

DESC PERSON;

Output:-

Name               Null?    Type
------------------- -------- ------------------
SNO                              NUMBER(10)

Now, let us rename the table from PERSON to STUDENT.

RENAME person TO student;

Now table name is changed and we can access it through the old name, we must use the new name.

DESC PERSON;

It gives the following error:-

ERROR:
ORA-04043: object PERSON does not exist

DESC STUDENT;

Output:-

Name                Null?    Type
------------------ -------- -------------------
SNO                             NUMBER(10)

Important Points

• The new name of the table must not already exist else we will get an error, ORA-00955: name is already used by an existing object.
• To rename a table, you must either be the database owner or the table owner.
• All DDL commands are executed in auto-commit mode. Hence once we rename a table then we can’t revert it back.
• 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.
• The RENAME TABLE statement is not allowed if there are any open cursors that reference the table that is being altered.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also See:-

• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint
• NOT NULL Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
• CHECK Constraint

Search