Add Column on Huge Partition Table Efficient way

I was worked on requirement where I need to add new column with default value and not null column.  Since Database is Operation data store database tables are very big size around in Terra bytes and millions of rows . Fortunately These tables are Partitioned Tables Which is life saver in my Case.

I want to Add New Column name is :  TRANID with default 9999 and enable Not Null.


Since Tables are Very big and partitioning so we can update the partitioning wise.



Step 1) Add column with out default value 

 ALTER TABLE SNANABALA.SUNIL_KUMAR ADD (TRANSACTIONID VARCHAR2(256));

Step 2 ) Get the Partitions Name

set echo off time off timing off feedback off trimspool on heading off pagesize 0 lines 250
spool 09_xSNANABALA_SUNILKUMAR_GET_PARTITIONS_NAME.log
select partition_name from dba_tab_partitions where TABLE_OWNER='SNANABALA' and table_name='SUNIL_KUMAR';
spool off;

exit;

-- In above sql will get all partitions names of SUNIL_KUMAR table. 

Step 3) Now we need to build dynamic sql to update each partition. 

FILE_DIR=Script location

PARTITION_FILE=${FILE_DIR}/09_xSNANABALA_SUNILKUMAR_GET_PARTITIONS_NAME.log

LOGFILE=${FILE_DIR}/Update_statements_SNANABALA_`date +%m%d%y_%H%M%S`.log

cd ${FILE_DIR}

echo "spool 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.LOG " > 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL

echo "set echo on feedback on time on timing on " >> 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL

echo "alter session enable parallel dml;" >> 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL

for LINE in `cat $PARTITION_FILE`

do

PARTNAME="`echo $LINE|cut -d'|' -f1`"

echo "UPDATE /*+ parallel(t,16) */ SNANABALA.SUNIL_KUMAR partition ( ${PARTNAME} ) t SET TRANSACTIONID = '99999';" >> 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL

echo "commit;" >> 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL

done;

echo "alter session disable parallel dml;" >> 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL

echo "spool off;" >> 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL

exit 0

Run above Shell Script 

./10_xSNANABALA_PARTITIONS_UPDATE.ksh

Step 4)  Now above shell script will Generate one sQL script 11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL 

 which needs to be executed in database

nohup sqlplus "/ as sysdba" @11_xSNANABALA_SUNIL_KUMAR_UPDATE_PARTITIONS.SQL &

Step 5)  Now validate the any rows having null values in newly added column

 select /*+ PARALLEL (a,16) */ count(*) from SNANABALA.SUNIL_KUMAR a where a.TRANSACTIONID  is null;

 If there are any null value rows returned above query then use the below command to set it to  99999 default value.

     

 update /*+ PARALLEL (a,16) */ SNANABALA.SUNIL_KUMAR a set TRANSACTIONID  =99999 where TRANSACTIONID  is null;

 commit;

step 6) Now we need to change columns as not null

set echo on time on timing on

spool 01_xSNANABALA_MODIFY_COLUMN_NOT_NULL_SNANABALA_TABLES.log

ALTER TABLE SNANABALA.SUNIL_KUMAR MODIFY (TRANSACTIONID VARCHAR2(256) not null);

spool off

exit

Step 7) if you want to add any indexes on that table you can create those .

You successfully added column to large partition table.