Renaming tables or columns in Oracle
Posted by decipherinfosys on August 7, 2007
Often times there is a need to rename the object either to adhere to naming conventions or to identify the object properly by giving some meaningful name. All the major RDBMS give functionality to rename the objects (table, columns, indexes etc.). In this blog we will cover how we can rename table, column, index and constraints in Oracle. First let us create table for our testing purpose. We will also create a stored procedure and trigger which will access this table.
CREATE TABLE NEW
ID NUMBER(9) NOT NULL,
CONSTRAINT PK_NEW PRIMARY KEY(ID)
CREATE OR REPLACE PROCEDURE test_proc
SELECT count(ID) INTO v_test_ID FROM NEW;
CREATE OR REPLACE TRIGGER TIB_NEW
BEFORE INSERT ON NEW
FOR EACH ROW
Following command will rename table:
SQL> RENAME NEW TO NEW_TEST;
SQL> ALTER TABLE NEW RENAME TO NEW_TEST;
SQL> desc new_test
Name Null? Type
—————————————– ——– ————-
ID NOT NULL NUMBER(9)
To rename a column:
SQL> ALTER TABLE NEW_TEST RENAME COLUMN ID TO TEST_ID;
We can use following command to rename an index. Let us check existing primary key constraint and corresponding index first.
SQL> select constraint_name, constraint_Type from user_constraints where table_name = ‘NEW_TEST’;
SQL> select index_name, index_Type from user_indexes where table_name = ‘NEW_TEST’;
Let us rename constraint first using following command.
SQL> ALTER TABLE new_test RENAME CONSTRAINT PK_NEW to PK_NEW_TEST;
Now if you re-run the above two SELECT statements to see the value of constraint and index, you will notice that constraint has been renamed but not the index. In Oracle, we need to rename primary key constraint and its associated index individually. We will rename the index now.
SQL> ALTER INDEX PK_NEW RENAME TO PK_NEW_TEST;
When table is renamed, its dependent objects become invalid. One can find out dependent objects of a table when table is renamed, by retrieving information from user_dependecies view. Following is the SQL to get the dependent objects so that we know in which other places we need to make corresponding change.
WHERE Referenced_Name = ‘NEW_TEST’
AND Referenced_Type = ‘TABLE’
Output will displayed as shown below. It indicates that we need to change trigger and procedure.
When column is renamed, getting the name of the procedures, views, triggers etc. is not available through user_dependencies view. For this we have to search in the source code of the object for a matching column name. Following is the SQL to get the dependent objects which depends on a column. Output also indicates line number we need to look at to make appropriate change.
SELECT Type, Name, Line
WHERE text like ‘%ID%’
TYPE NAME LINE
———— —————————— ———-
PROCEDURE TEST_PROC 5
TRIGGER TIB_NEW 6
TRIGGER TIB_NEW 7
One Response to “Renaming tables or columns in Oracle”
Sorry, the comment form is closed at this time.