➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
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;
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,
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.
It gives the following error:-
ORA-04043: object PERSON does not exist
Name Null? Type ------------------ -------- ------------------- SNO NUMBER(10)
- 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!