Redefine Table Online

Redefining Tables Online in  Oracle

We know there is a procedure given to us to redefine a table for online range partition on link.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm

But it is bit complex and time consuming. One have to consider many steps before doing online partition. All these steps are compulsory. When I came across these steps it took me several hours to go through each and every bit. Then I decided to write a unix script which can generate another script. you can either copy and past all the steps in generated script without any worry or you can just run the script from sql prompt.

Unix script is given below. you need to run it on oracle server

redefine_table.sh

#!/bin/bash
 TABLE_SCHEMA=$1
 PARTITION_COLUMN=$3
 TABLE_NAME=$2
 INTERM_TABLE_NAME=$2'_INTERM'

INDX_CNT=`sqlplus -S -L << EOF / as sysdba
 set long 15600
 set lines 5000
 set pagesize 0
 select count(1) from dba_indexes where Table_name='$TABLE_NAME' and index_name not like '%PK%';
 exit;
 EOF`

DDL_GRANTS=`sqlplus -S -L << EOF / as sysdba
 set long 1560
 set pagesize 0
 column PRIVI format a3000 word_wrapped
 SELECT 'GRANT '||PRIVI||' ON '||TABLE_NAME||' TO '||GRANTEE||';' as OBJ_DDLS FROM ( SELECT GRANTEE,TABLE_NAME, LISTAGG (PRIVILEGE, ',') WITHIN GROUP (ORDER BY PRIVILEGE) PRIVI from DBA_TAB_PRIVS WHERE TABLE_NAME='$TABLE_NAME' GROUP BY GRANTEE,TABLE_NAME);
 exit;
 EOF`

DDL_OBJ=`sqlplus -S -L << EOF / as sysdba
 set long 1560
 set pagesize 0
 column ddl_object format a3000 word_wrapped
 select DBMS_METADATA.GET_DDL('SYNONYM',synonym_name,OWNER)||';' as ddl_object FROM DBA_SYNONYMS WHERE TABLE_NAME='$TABLE_NAME';
 exit;
 EOF`

PARTITION_DATE=(`sqlplus -S -L << EOF / as sysdba
 set pause off pages 0 echo off verify off feed off termout off
 select trunc(min($PARTITION_COLUMN),'month') from $TABLE_SCHEMA.$TABLE_NAME;
 exit;
 EOF`)

TABLE_PK=(`sqlplus -S -L << EOF / as sysdba
 set pause off pages 0 echo off verify off feed off termout off
 column CONSTRAINT_NAME format a3000 word_wrapped
 select CONSTRAINT_NAME from dba_constraints where table_name='\$TABLE_NAME' and owner='\$TABLE_SCHEMA' and constraint_type='P';
 exit;
 EOF`)

TABLE_INTERM_PK=$TABLE_PK'_INTERM'

TABLESPACE_NAME=(`sqlplus -S -L << EOF / as sysdba
 set pause off pages 0 echo off verify off feed off termout off
 select TABLESPACE_NAME from dba_tables where TABLE_NAME='\$TABLE_NAME' and owner ='\$TABLE_SCHEMA';
 exit;
 EOF`)

echo "/**************Verifying Table Okay for Redfinition***********************/"
echo "/**************************************************************************/"
echo 'set serveroutput on'
echo 'BEGIN'
echo "DBMS_REDEFINITION.CAN_REDEF_TABLE('$TABLE_SCHEMA','$TABLE_NAME',DBMS_REDEFINITION.CONS_USE_PK);"
echo $'END;'
echo $'/'
echo $'\n'

DDL_INTERM=`sqlplus -S -L << EOF / as sysdba
 set long 3000
 set pagesize 0
 select substr(ddl,1,INSTR(ddl,'COMPUTE')-1) as ddl_cons from (select replace(replace(ddls,'\$TABLE_NAME','\$INTERM_TABLE_NAME'),'\$TABLE_PK','\$TABLE_INTERM_PK') as DDL from (select DBMS_METADATA.GET_DDL('TABLE','\$TABLE_NAME','\$TABLE_SCHEMA') as ddls from DUAL)) ;
 exit;
 EOF`

echo "/**************Creating Interm Table***********************/"

echo $DDL_INTERM
 echo ") PARTITION BY RANGE (QUEUE_TIMESTAMP)
 INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (
 PARTITION First_Partition values LESS THAN (TO_DATE('$PARTITION_DATE','DD-MON-YYYY'))
 ) TABLESPACE $TABLESPACE_NAME;"

echo $'\n'
echo $'\n'

echo $'/***********************Redefining Table this process will move data as well**************************************/\n'

echo "EXEC DBMS_REDEFINITION.START_REDEF_TABLE('$TABLE_SCHEMA', '$TABLE_NAME','$INTERM_TABLE_NAME','',dbms_redefinition.cons_use_pk);"

echo $'\n'
echo $'/***********************Copying Dependent Objects. This will automatically create any triggers, materialized view logs , grants and constraints but not indexes *******************************/\n'

echo "EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('$TABLE_SCHEMA', '$TABLE_NAME','$INTERM_TABLE_NAME',2,'$1','$TABLE_PK','$TABLE_INTERM_PK');"
echo "EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('$TABLE_SCHEMA', '$TABLE_NAME','$INTERM_TABLE_NAME',3,'$1','$TABLE_PK','$TABLE_INTERM_PK');"

echo $'\n'

echo 'Declare'
echo 'num_errors PLS_INTEGER;'
echo 'BEGIN'
echo "DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('$TABLE_SCHEMA', '$TABLE_NAME','$INTERM_TABLE_NAME',0, TRUE, TRUE, TRUE, TRUE, num_errors);"
echo 'END;'
echo '/'

echo $'/********************************Querying the DBA_REDEFINITION_ERRORS view to check for errors.*******************************************/\n'
echo "select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;"

echo $'\n'
echo $'\n'

echo $'/********************************generate ddl for remaining non primary key index(es) and register*******************************************/'

DDL_INDX=`sqlplus -S -L << EOF / as sysdba 
set long 15600 set lines 5000 set pagesize 0 column IDX_DDLS format a3000 word_wrapped 
select DBMS_METADATA.GET_DDL('INDEX',index_name,'$TABLE_SCHEMA')||';' as IDX_DDLS 
from dba_indexes 
where Table_name='$TABLE_NAME' 
and index_name not like '%PK%'; exit; EOF` 
echo $'/********************************Synchronize table and complete.*******************************************/\n' 
echo $'/********************************This now bring over updates, but still not bring one after*******************************************/\n' 
echo "EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('$TABLE_SCHEMA', '$TABLE_NAME','$INTERM_TABLE_NAME');" 
echo $'\n' echo $'/********************************This is final sync and switch name of tables*******************************************/\n' 
echo "EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('$TABLE_SCHEMA', '$TABLE_NAME','$INTERM_TABLE_NAME');" 
echo $'\n' echo "/************At this stage CHANGE_QUEUE_LOG_INTERM is the original table renamed. This can be dropped once no more long running queries running.*****************/" 
echo '/********************************Drop tables to avoid constraint issues - You may want to re-export first ??*******************************************/' 
echo "DROP TABLE $TABLE_SCHEMA.$INTERM_TABLE_NAME;" 
echo "/***********POST Redefination Tasks ***************************/" 
echo $'/********************************Create remaining non primary key index(es) and register*******************************************/\n' 
echo "/***********create indexes,generate grants and run the output to re grant privileges on table ***************************/" 
echo $'\n' for i in 1 $INDX_CNT; do echo $DDL_INDX|awk -F ";" '{print $'$i'";"}' 
echo $'\n' done echo $DDL_GRANTS echo $DDL_OBJ echo "/************Get statistics *****************/" 
echo "EXEC dbms_stats.gather_table_stats('$TABLE_SCHEMA', '$TABLE_NAME');"

Running the script.

  1. copy the above script and create a file called redefine_table.sh
  2. now you can issue following command to run the script in order to produce another script containing all sql commands to redefine the table.
./redefine_table.sh [schema name] [table to be redefined] [column name date type for partitions]>redefine_table.sql

3. now you can run commands one by one from redefine_table.sql or you may run whole script.

2 thoughts on “Redefine Table Online”

  1. Hi Naveed.
    Awesome script! Was looking for something like this to avoid tedious manual creation 🙂

    btw I thought you could copy indexes during the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS stage but you have set copy_indexes to 0 – jut wondering if there is any reason for this?

    cheers,
    brian

    1. Hi Brian,
      Thanks for you comments and appreciation , well at that time I prefered to create indexes manually due to some user defined named indexes but yes we can copy the indexes as well.

      Thanks and Regards
      Naveed

Leave a Reply to naveed Cancel reply

Your email address will not be published. Required fields are marked *