Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

Log onto the database as SCOTT/TIGER user and use the following command to create an EmpTest table from an existing Emp table:

create table EmpTest as select * from Emp;

Now add a primary key on the EMPNO column and a unique constraint on the ENAME and SAL columns:

alter table EmpTest add constraint EmpTest_PK primary key (empNo); alter table EmpTest add constraint EmpTest_AK1 unique (eName, sal);

To get the structure of the EmpTest table, issue the following command:

desc EmpTest

This produces the following list of the table's columns:

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

Because SAL name is not descriptive enough, rename this column SALARY. Reconnect as SYS user before issuing any SQL commands, because later you will go to the Oracle data dictionary and you'll need to be a SYS user to have the necessary privileges.

Step 1: Identify All Database Objects That Depend on the Column You Plan to Rename
First, find out if the SAL column is part of any constraints of the EMPTEST table in the SCOTT schema:

select t.constraint_name, t.constraint_type as type, c.column_name, c.position from dba_constraints t, dba_cons_columns c where t.table_name = 'EMPTEST' and t.owner = 'SCOTT' and t.constraint_name = c.constraint_name and t.table_name = c.table_name and t.owner = c.owner and t.constraint_name in (select c.constraint_name from dba_constraints t, dba_cons_columns c where t.table_name = 'EMPTEST' and t.owner = 'SCOTT' and c.column_name = 'SAL' and t.constraint_name = c.constraint_name and t.table_name = c.table_name and t.owner = c.owner) order by c.constraint_name, c.position

Listing 1 shows the output of the above code:

CONSTRAINT_NAME TYPE COLUMN_NAME POSITION -------------------------- ----- --------------------- -------- EMPTEST_AK1 U ENAME 1 EMPTEST_AK1 U SAL 2

Now you know that the SAL column is a part of the EMPTEST_AK1 unique constraint. You need to drop this constraint before renaming the column.

Determine Whether the Column Has Constraints or Indexes
To find out if this column is part of any indexes, the following SQL statement:

select index_name, column_name, column_position as position from dba_ind_columns where table_owner = 'SCOTT' and table_name = 'EMPTEST' and index_name in (select index_name from dba_ind_columns where table_owner = 'SCOTT' and table_name = 'EMPTEST' and column_name = 'SAL') order by index_name, position

Listing 2 shows the output of the above code:

INDEX_NAME COLUMN_NAME POSITION -------------------------- ---------------------------- -------- EMPTEST_AK1 ENAME 1 EMPTEST_AK1 SAL 2

In this case, only one index EMPTEST_AK1 depends on the SAL column. the database implicitly creates this index to support the EMPTEST_AK1 unique constraint, and it will be dropped when you drop the constraint.

Find Out If Any Views Depend on the EMPTEST Table
You need to check if any views are based on the EMPTEST table and whether they depend on the SAL column. The following SQL command will verify the first part:

select view_name from dba_views where owner = 'SCOTT';

In order to see if any views actually depend on the SAL column, use the TEXT column of DBA_VIEWS in the SQL WHERE clause. Unfortunately, you can't do it directly in SQL because the TEXT column's type is LONG. However, you can find out using the following simple PL/SQL block:

begin for rec in (select view_name, text from dba_views where owner = 'SCOTT') loop if instr(upper(rec.text), 'SAL') > 0 then dbms_output.put_line(rec.view_name); end if; end loop; end;

In this case, the block does not generate any output, because no views were defined on the EMPTEST table.

Verify Whether the SAL Column Is Used in Any PL/SQL Code
You have to check if the SAL column is used in any PL/SQL code. The following query gives all the occurrences of the word SAL in the TEXT column that contains the code of a PL/SQL unit, which is identified in the NAME column:

select name, text from dba_source where owner = 'SCOTT' and upper(text) like '%SAL%'

Then you have to look at each row to determine if it really uses the SAL column of your EMPTEST table, and decide whether you need to change the code.

You've determined that the only database object that depends on the EMPTEST.SAL column is the unique constraint EMPTEST_AK1. Drop it now; you'll recreate it later:

alter table SCOTT.EmpTest drop constraint EmpTest_AK1;

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