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,
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
One Response to “Renaming tables or columns in Oracle”
Sorry, the comment form is closed at this time.
Re-naming objects in SQL Server « Systems Engineering and RDBMS said
[…] Renaming tables or columns in Oracle […]