DBMS_REDEFINITION – Partitioned Table

CREATE TABLE DBATST.TEMP_TABLE_INTERIM
 (
 "TLO_ID"             NUMBER ,
 "MARKUP_TIMESTAMP"     TIMESTAMP (6),
 "TRACE_LEVEL"         NUMBER(1,0),
 "TRACE_TEXT"         VARCHAR2(4000 CHAR),
 "CREATED_BY"         VARCHAR2(64 CHAR),
 "CREATED_DATE"         DATE,
 "MODIFIED_BY"         VARCHAR2(64 CHAR),
 "MODIFIED_DATE"     DATE,
 "PRC_ID"             NUMBER
 )
 PARTITION BY RANGE (PRC_ID) INTERVAL (5000)
 ( PARTITION BEFORE_PRC_ID_5000 VALUES LESS THAN (5000))
 tablespace USERS ;
set pages 10000
 set head on
 set lin 150
 col owner format a10
 col table_name format a20
 col partitioned format a12
spool REDEF_TEMP_TABLE.log
 alter session set db_file_multiblock_read_count=128;
select owner, table_name, partitioned
 from dba_tables where table_name like 'TEMP_TABLE%';
 set head off
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') START_PROCESS from dual;
prompt "COUNTING ROWS table TEMP_TABLE before REDEF...";
create table tt_check_rows_trc
 (rows_before number, rows_after number,dt_ini date,dt_fin date);
insert into tt_check_rows_trc (rows_before,dt_ini)
 select count(*) ROWS_ORIG_TABLE,sysdate from DBATST.TEMP_TABLE;
 commit;
-- 1.) NOLOGGING
 prompt "Nologging INTERIM table ...";
 alter table DBATST.TEMP_TABLE_INTERIM nologging;
-- 2.) Checking if can redef:
 prompt "Executing CAN_REDEF table ...";
 EXEC DBMS_REDEFINITION.can_redef_table('DBATST', 'TEMP_TABLE');
-- 3.) Start REDEF
 prompt "Executing START REDEF table ...";
 BEGIN
 DBMS_REDEFINITION.start_redef_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 4.) Sync TABLE
 prompt "Executing SYNC table ...";
 BEGIN
 dbms_redefinition.sync_interim_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 5.) Copy Dependencies
 prompt "Executing COPY DEPENDENTS table ...";
 SET SERVEROUTPUT ON
 DECLARE
 l_errors  NUMBER;
 BEGIN
 DBMS_REDEFINITION.copy_table_dependents(
 uname            => 'DBATST',
 orig_table       => 'TEMP_TABLE',
 int_table        => 'TEMP_TABLE_INTERIM',
 copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
 copy_triggers    => TRUE,
 copy_constraints => TRUE,
 copy_privileges  => TRUE,
 ignore_errors    => FALSE,
 num_errors       => l_errors,
 copy_statistics  => FALSE,
 copy_mvlog       => FALSE);
if l_errors <> 0 then
 raise_application_error(-20001,'Error on DBMS_REDEFINITION.copy_table_dependents' ||SQLCODE||' - ERROR - '||SQLERRM);
 end if;
END;
 /
-- 7.) Sync TABLE
prompt "Executing SYNC table ...";
 BEGIN
 dbms_redefinition.sync_interim_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 8.) Finishing REDEF
prompt "Executing FINISH REDEF table ...";
 BEGIN
 dbms_redefinition.finish_redef_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 9.) Logging table
 prompt "Executing LOGGING table ...";
 alter table DBATST.TEMP_TABLE logging;
-- 10.) Finishing the process
 prompt "COUNTING ROWS table TEMP_TABLE after REDEF...";
 set head on
update tt_check_rows_trc
 set rows_after=(select count(*) ROWS_REDEF_TABLE 
                  from DBATST.TEMP_TABLE), dt_fin=sysdate;
 commit;
 select 
to_char(dt_ini,'dd-mm-yyyy hh24:mi:ss') Start_Date, 
rows_before,to_char(dt_fin,'dd-mm-yyyy hh24:mi:ss') End_Date, 
rows_after 
from tt_check_rows_trc;
 select 
to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') END_PROCESS 
from dual;
 select owner, table_name, partitioned 
from dba_tables 
where table_name like 'TEMP_TABLE%';
drop table tt_check_rows_trc;
 spool off
 prompt " LOG of this execution on  REDEF_TEMP_TABLE.log"
 prompt " End of process"
 exit
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s