Column Rename 9i Oracle

Renaming a Column in 9i

Administration Tips

How do I rename a column in Oracle 9i? In Oracle 9i, you can now perform an in-place rename for any or all columns, and the entire process uses a new package called dbms_redefinition. Step 1: Find out whether the table you want to alter is capable of being altered. To do that, you issue the following command: EXECUTE DBMS_REDEFINITION.CAN_REDEF_TABLE(‘SCHEMA’,’TABLE NAME’)

...though you obviously enter appropriate values for the schema and table names there. If that procedure doesn’t report any errors, then you can proceed with the next steps. Step 2: create a new table that looks like what you want the original table to be when the redefinition process is over (i.e., create it with columns using the new names, or take the opportunity to specify new storage parameters and so on). This is a ‘holding table’ which will eventually be swapped with the original. It must not contain any data -we are interested merely in its definition. The holding table must exist within the same schema as the original one being modified. Step 3: run the dbms_redefinition.start_redef_table package/procedure to describe the real table, its holding table counterpart, and any column mapping information needed. For example: EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE

(‘SCOTT’,’EMP’,’HOLDING_EMP’, -

‘EMPNO EMPLOYEE_NO’, ‘ENAME ENAME’, ‘MGR MANAGER_NO’ …AND SO ON…) Note here how two columns are being re-named, but one of them isn’t. Also note the use of the minus sign at the end of each line -that's just a continuation character. Without it, the package/procedure will attempt to run before you've typed in the entire remapping data. Step 4: run the dbms_redefinition.finish_redef_table procedure, supplying the schema, the original table name and the holding table name as the parameters: EXECUTE DBMS_REDEFINITION.FINISH_REDEF_TABLE

(‘SCOTT’,’EMP’,’HOLDING_EMP’)

And at the end of that procedure, you'll be able to select from the original table, and see that the columns really have been changed. There are some nasties to watch out for when doing this, however. Copyright © Howard Rogers 2001

10/17/2001

Page 1 of 4

Renaming a Column in 9i

Administration Tips

Firstly, any constraints that exist on the original table must be defined on the holding table, too, if you want them to be there at the end of the exercise. But, since this is all taking place in the one schema, if you were to name your primary or key constraints for the holding table the same as on the real table, the indexes that get created when such constraints are created would end up with the same name… so the constraint names must be different. (Incidentally, if you’ve any triggers or secondary indexes on the original table, they must be created on the holding table, too. Again, this means index names change). Second, any foreign key constraints you want preserved also have to be created on the holding table -but they must be set to be DISABLED (they get automatically enabled at the end of the process). Third, the mechanism Oracle uses to make this all happens is a materialized view. The trouble is, you can’t (as of 9.0.1) redefine a table on which a legitimate materialized view has been created (it produces an ORA-12091 error if you try it). That might not be a problem, except that the entire redefinition process starts by taking a snapshot (i.e., a materialized view) of the original table. What that means is that if anything goes wrong with the redefinition (perhaps a syntax error in line 128 of the start_redef procedure script), a materialized view has already been created on the source table. And what that means is that a second attempt at redefinition, with the syntax fixed up, fails for the 12091 reason. That is fairly easily fixed, though, by aborting the entire procedure. To do that, you run the following command: EXECUTE DBMS_REDEFINITION.ABORT_REDEF_TABLE

(‘SCOTT’,’EMP’,’HOLDING_EMP’)

Fourth -at the end of the process, you'll be left with the holding table stuffed full of data, looking exactly as the original table did (i.e., with all the old columns names and so on). That table is just wasting space, so you need to drop it -but that's not done automatically for you, so you'll have to remember to issue your own 'drop table holding_emp', for example. Fifth, any DML being applied to the original table during this entire process is being recorded in a journaling table, and will be applied to the resulting table -but if there’s vast amounts of DML involved, then you ought to use the sync_interim_table procedure to periodically keep the interim table up-to-date with the changes being made.

Online Table Redefinition Restrictions Tables that do not have a primary key are not supported (I imagine that this is because any on-going DML is logged in an Index Organised Table, which can only be created with a primary key).

Copyright © Howard Rogers 2001

10/17/2001

Page 2 of 4

Renaming a Column in 9i

Administration Tips

Any faintly exotic data types are suspect -for example, data types you’ve defined yourself, bfiles and longs (LOBS are OK) can’t be re-defined online. Your new table gets all of the rows in the original table -there’s no facility to stick a where clause in, and only get a subset of them. And, finally, any new columns added to the table are obviously not going to have any source data in the original table -so, were you to try and define such columns (on the holding table) as NOT NULL, you can expect the thing to fail.

Simple Demo SQL> CONNECT CONNECTED.

SCOTT/TIGER@HJR

SQL> CREATE TABLE R_TEST( 2 COL1 CHAR(5) CONSTRAINT R_TEST_PK 3 COL2 NUMBER(7,2)); TABLE CREATED. SQL> SQL> SQL>

INSERT INTO R_TEST VALUES

SQL>

SELECT

INSERT INTO R_TEST VALUES

PRIMARY KEY,

('AAAA',1238.90); ('BBBB',4329.30);

COMMIT;

*

FROM R_TEST;

COL1 COL2 ----- ---------AAAA 1238.9 BBBB 4329.3 SQL> CREATE TABLE RTEST_HOLD( 2 DEPT_CODE CHAR(5), 3 SALARY NUMBER(7,2)); TABLE CREATED. SQL> EXECUTE DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','R_TEST') PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. SQL> EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE( > 'SCOTT','R_TEST','RTEST_HOLD', > 'UPPER(COL1) DEPT_CODE,> COL2 SALARY') PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Copyright © Howard Rogers 2001

10/17/2001

Page 3 of 4

Renaming a Column in 9i

Administration Tips

SQL> EXECUTE DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','R_TEST','RTEST_HOLD') PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. SQL> SELECT * FROM R_TEST; DEPT_ SALARY ----- ---------AAAA 1238.9 BBBB 4329.3 SQL> TABLE

DROP TABLE RTEST_HOLD; DROPPED.

SQL> DESC R_TEST; NAME -------------------------------DEPT_CODE SALARY

Copyright © Howard Rogers 2001

TYPE ---------------------------CHAR(5) NUMBER(7,2)

10/17/2001

Page 4 of 4