Renaming tables or columns in Oracle

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,
SECURE_ID NUMBER(9),
NAME      VARCHAR(10),
CONSTRAINT PK_NEW PRIMARY KEY(ID)
);

CREATE OR REPLACE PROCEDURE test_proc
IS
v_test_id number;
BEGIN
SELECT count(ID) INTO v_test_ID FROM NEW;
END;
/

CREATE OR REPLACE TRIGGER TIB_NEW
BEFORE INSERT ON NEW
FOR EACH ROW
BEGIN
SELECT REVERSE(:NEW.ID)
INTO :NEW.SECURE_ID
FROM DUAL;
END;
/

Following command will rename table:

SQL> RENAME NEW TO NEW_TEST;

Table renamed.

OR

SQL> ALTER TABLE NEW RENAME TO NEW_TEST;

Table altered.

SQL> desc new_test
Name                                      Null?    Type
—————————————– ——– ————-
ID                                        NOT NULL NUMBER(9)
NAME                                               VARCHAR2(10)

To rename a column:

SQL> ALTER TABLE NEW_TEST RENAME COLUMN ID TO TEST_ID;

Table altered.

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’;

CONSTRAINT_NAME                C
—————————— –
PK_NEW                         P

SQL> select index_name, index_Type from user_indexes where table_name = ‘NEW_TEST’;

INDEX_NAME                     INDEX_TYPE
—————————— —————————
PK_NEW                         NORMAL

Let us rename constraint first using following command.

SQL> ALTER TABLE new_test RENAME CONSTRAINT PK_NEW to PK_NEW_TEST;

Table altered.

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;

Index altered.

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.

SELECT Type,Name
FROM USER_DEPENDENCIES
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.

TYPE              NAME
—————– ——————————
PROCEDURE         TEST_PROC
TRIGGER           TIB_NEW

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
FROM user_source
WHERE text like ‘%ID%’
/

Output is

TYPE         NAME                                 LINE
———— —————————— ———-
PROCEDURE    TEST_PROC                               5
TRIGGER      TIB_NEW                                 6
TRIGGER      TIB_NEW                                 7