change a table and save off the data–how to guide- oracle syntax

A classic situation when you create a table for a development requirement and then you need to add a couple of columns, but don’t want to lose your data.  There is a couple of ways of getting this done.  I’d do “ALTER TABLE ADD column” operations.  This is super simple and I’ll outline the procedure with pseudo code.

First drop constraints and indexes on the table.

select 'ALTER TABLE ‘ || owner || ‘.’ || table_name || ‘ DROP CONSTRAINT ' || constraint_name ||';' from all_constraints where table_name = 'F55MYRXX' and owner = 'TESTDTA';

alter table testdta.F55myrxx drop constraint F55MYRXX_PK;

select 'DROP INDEX ' || owner || '.' || index_name || ';' from all_indexes where table_name = 'F55MYRXX' and owner = 'TESTDTA';

DROP INDEX TESTDTA.F55MYRXX_0;

alter table testdta.F55MYRXX rename to F55MYRXXBAK ;

--so now you have your table as a BAK – cool – this is ready to insert the data from (or use it to alter and then generate the indexes again).

Note that you need to do the above, as the rename does not rename the constraints or the PK’s that they will give you problems when you go to generate the table from E1.

Generate the new table structure from OMW

Now, see the column differences:

select column_name from all_tab_columns where table_name ='F55MYRXX' and owner = 'TESTDTA'
minus
select column_name from all_tab_columns where table_name ='F55MYRXXBAK' and owner = 'TESTDTA';

COLUMN_NAME                   
------------------------------
XXIDNUM1                      
XXIDNUM2                      
XXIDNUM3                      
XXIDNUM4 

Note that if you get nothing, you’ve done SOMETHING WRONG.  Delete your global tables and dddict and ddtext files on your fatty and try again.  Perhaps get the specs of the table.  You should get column differences.

So, you can use this information as you’d like.  Either copy back in your data, or if you have heaps, run some alter table statements.  You need to know the oracle data types if you are going to execute alter table, get these with the statement below:

select * from all_Tab_columns where owner = 'TESTDTA' and table_name = 'F55MYRXX' and column_name in (
select column_name from all_tab_columns where table_name ='F55MYRXX' and owner = 'TESTDTA'
minus
select column_name from all_tab_columns where table_name ='F55MYRXXBAK' and owner = 'TESTDTA');

Then alter your backup table, or use the alter statements for the future environments.

select 'ALTER TABLE ' || owner || '.' || TABLE_NAME || 'BAK ADD (' || COLUMN_NAME || ' ' || DATA_TYPE || '(' || data_length || ') );'  from all_Tab_columns where owner = 'TESTDTA' and table_name = 'F55MYRXX' and column_name in (
select column_name from all_tab_columns where table_name ='F55MYRXX' and owner = 'TESTDTA'
minus
select column_name from all_tab_columns where table_name ='F55MYRXXBAK' and owner = 'TESTDTA');

run the output:

ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM1 NCHAR(40) );
ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM2 NCHAR(40) );
ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM3 NCHAR(40) );
ALTER TABLE TESTDTA.F55MYRXXBAK ADD (XXIDNUM4 NCHAR(40) );

Then your backup table has all of the columns.  Note that you might need to do some cool things with 1 char strings and the like, but you get the picture.  The cool thing about this method is that you can now insert you data back with a very simple command [as you’ve added the columns to the backup]:

insert into TESTDTA.F55MYRXX select * from TESTDTA.F55MYRXXBAK ;
commit;

Once you have the above, you could use it for all your subsequent environments without all of the drop malarky, just change the owner name.

The other way to do this is to do an insert into select * from and list all of the columns

insert into TESTDTA.F55MYRXX (COL1, COL2…) select COL1, COL2…,0,’’,’’,0 from TESTDTA.MYRXXBAK;

Note that the ‘’,0,’’,0 are for the new columns at the end of the table.