How to Rename a Table in Oracle

Step-by-Step Guide

1. 1

   Step 1: Find out which version of Oracle you have installed.

   Plan to use at least version 8i of Oracle.
   
   This is the earliest version that supports the function that permits renaming a table in the program.
2. 2

   Step 2: Follow the requirements in order to rename a table in Oracle.

   One of the major pre-requisites is that the table must be in your own schema.
   
   There are also certain types of synonyms that you can not rename in Oracle.
   
   These include public synonyms as well as types with dependent tables. , You will need to know the table name in order to enter the command that allows you to alter it. , If you are unsure of dependencies, you can use the “TOAD” tool to check the table’s status.
   
   First, select the table you plan to rename in the schema browser, Then find the “Used By” tab.
   
   Any dependencies will be listed in this section. , Keep in mind if you attempt to use the same schema, you will receive an error message in Oracle.
   
   The error message will appear on screen as the following:
   ORA-14047:
   ALTER TABLE|INDEX RENAME may not be combined with other operations.
   
   For example, “priority.table_name” can not be changed to “priority.new_table_name.” , You will type in the following to make the adjustment: “ALTER TABLE original_table_name RENAME TO new_table_name;” The original name will represent the former table, sequence, view or private synonym. , Once you have renamed a table, all of the objects including indexes should move with the change.
   
   If the renaming is not successful, you will have to perform some troubleshooting to determine why you are receiving an error message.
3. 3

   Step 3: Review the original syntax for the table that you wish to rename.

4. 4

   Step 4: Determine if there are any dependencies prior to renaming a table in Oracle.

5. 5

   Step 5: Choose a new table name.

6. 6

   Step 6: Enter the rename command into Oracle in order to alter the table name.

7. 7

   Step 7: Check the success of entering the syntax to rename a table in Oracle.

Detailed Guide

About the Author