Tuning by Batch processing in PL/sqL (BULK COLLECT, FORALL and Associative Arrays)

Tuning Batch processing in PL/sqL
(BULK COLLECT, FORALL and Associative Arrays)

How to partition an existing table using re-definition

partition an existing table using re-definition
-----------------------------------------------------

1. CHECK PARTITIONED TABLE IS redefinition OR NOT

exec dbms_redefinition.can_redef_table('STLBAS', 'STTRNDTL_TEST');

2. CREATE interim TABLE (TEMPORARY TABLE) WITH PARTITION DESING
DROP MATERIALIZED VIEW STTRNDTL_TEST
DROP TABLE STTRNDTL_TEST_1
CREATE TABLE STTRNDTL_TEST_1
(
COMPCODE VARCHAR2(3 BYTE) ,
DOCTTYPE VARCHAR2(2 BYTE) ,
SUBTTYPE NUMBER(2) ,
DOCNUMBR VARCHAR2(8 BYTE) ,
DOCTDATE DATE ,
SERLNUMB NUMBER(5) ,
ACCTCODE VARCHAR2(10 BYTE) ,
DOCDUEDT DATE ,
COSTCODE VARCHAR2(10 BYTE) ,
SUBLEDCD VARCHAR2(6 BYTE) ,
TRANCODE VARCHAR2(3 BYTE) ,
NARATION VARCHAR2(200 BYTE) ,
DBCRCODE VARCHAR2(1 BYTE) ,
CURRCODE VARCHAR2(3 BYTE) ,
EXCGRATE NUMBER(13,9) ,
JVFCAMNT NUMBER(16,3) ,
JVLCAMNT NUMBER(16,3) ,
ORGAMTFC NUMBER(13,3) ,
ORGAMTLC NUMBER(13,3) ,
ADJAMTFC NUMBER(13,3) ,
ADJAMTLC NUMBER(13,3) ,
ORGBAKFC NUMBER(13,3) ,
ORGBAKLC NUMBER(13,3) ,
REFDOCTY VARCHAR2(3 BYTE) ,
REFDCSTY NUMBER(2) ,
REFDOCNO VARCHAR2(25 BYTE) ,
REFSRLNO NUMBER(6) ,
REFDOCDT DATE ,
REFDCNUM VARCHAR2(25 BYTE) ,
REFRDATE DATE,
INTDOCTY VARCHAR2(3 BYTE),
INTDCSTY NUMBER(2),
INTDOCNO VARCHAR2(8 BYTE),
INTDOCDT DATE,
CTRLMCRO VARCHAR2(2 BYTE),
STATCODE VARCHAR2(1 BYTE),
POSTDATE DATE,
PURGFLAG VARCHAR2(1 BYTE),
TRANSERL NUMBER(8),
ALLCFREZ VARCHAR2(1 BYTE),
LMODUSER VARCHAR2(6 BYTE),
LMODDATE DATE,
CLERFLAG VARCHAR2(1 BYTE),
OPRSTAMP VARCHAR2(6 BYTE),
TIMSTAMP DATE ,
AUTHBYID VARCHAR2(6 BYTE),
ORGRESBR VARCHAR2(3 BYTE),
AUTHRBID VARCHAR2(6 BYTE),
RESPDATE DATE,
INTSRLNO NUMBER(5),
NETWUSER VARCHAR2(30 BYTE),
AUTHFLAG VARCHAR2(1 BYTE)
)TABLESPACE ORBITS
Partition by range(DOCTDATE)
(
partition JAN2009 values less than (to_DATE('01-01-2009','DD-MM-RRRR')),
partition FEB2009 values less than (to_DATE('01-02-2009','DD-MM-RRRR')),
partition MAR2009 values less than (to_DATE('01-03-2009','DD-MM-RRRR')),
partition UPTOMAX values less than (maxvalue));


3. START REDEFINATION (TRANSFER DATA TO INTERIM TABLE FROM MAIN TABLE

--exec dbms_redefinition.start_redef_table('SCHEMA_NAME',
'MAIN_TABLE', 'interim TABLE');
exec dbms_redefinition.start_redef_table('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1');

4. Copies the dependent objects of the original table onto the interim table

declare
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1',1, true, true, true, false,error_count);
dbms_output.put_line('errors := ' to_char(error_count));
END;
/

5. Completes the redefinition process (FOR COPYING THE ITERIM TABLE STRUCTURE TO MAIN TABLE)
exec dbms_redefinition.finish_redef_table('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1');

6.Drop the interim table STTRNDTL_TEST_1
DROP TABLE STTRNDTL_TEST_1;

7 CHECK

Select * from user_tab_partitions ;

select index_name , status
from user_indexes
where table_name='STTRNDTL_TEST';

---------------------------------------------------------------

--- IF ERROR OCCURR LIKE
ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views
6. Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by

select log_table from user_snapshot_logs;
select master,log_table from user_mview_logs;

DROP MATERIALIZED VIEW LOG ON STTRNDTL_TEST;
or
exec dbms_redefinition.abort_redef_table('STLBAS', 'STTRNDTL_TEST', 'STTRNDTL_TEST_1');