Browse DevX
Sign up for e-mail newsletters from DevX


Rename a Table Column Without Any Data Manipulation-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Step 4: Update the Oracle Dictionaries
The final step is to update the Oracle dictionaries by running Oracle's catalog.sql script:


Also, you must update the data dictionary cache in case the table's definition was already cached. To do this, flush the shared pool:

alter system flush shared_pool;

Now you can log on to the database via SQL*Plus as SCOTT/TIGER user and check on the structure of the EMPTEST table:

desc EmpTest

The output is:

Name Null? Type --------------------------------- -------- ------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SALARY NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)

As you can see, column SAL has been successfully renamed to SALARY.

Step 5: Recreate All Previously Dropped Database Objects
The only step left is to recreate all the database objects that were dependent on the EMPTEST.SAL column and that dropped during Step 1. Recreate the unique constraint EmpTest_AK1 you dropped:

alter table EmpTest add constraint EmpTest_AK1 unique (eName, salary);

That's it! You've renamed a column quickly and without having to manipulate the data in that table. The only potentially time-consuming process when you deal with large tables is recreating indexes.

Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date