DECLARE
v_sql VARCHAR2(20000);
v_logfile VARCHAR2(200) := ‘exchange_sbf_partition.log’;
v_min_process_date NUMBER(10);
v_max_process_date NUMBER(10);
v_src_count NUMBER(10);
v_trg_count NUMBER(10);
CURSOR c_get_parts IS
SELECT partition_name
FROM dba_tab_partitions
WHERE table_owner = ‘WHOUSE2’
AND table_name = ‘SPORTS_BET_FACT_TOTE’
AND partition_name >= ‘FY20092010’
–AND partition_name <= ‘P20110926’
ORDER BY 1;
BEGIN
FOR r_parts IN c_get_parts
LOOP
v_sql := ‘DROP TABLE whouse1.sbsft_tmp1 PURGE’;
prod_run.d_log.WRITE
( p_text => v_sql
, p_file => v_logfile );
BEGIN
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
prod_run.d_log.WRITE
( p_text => ‘Create temp table from partition ‘ || r_parts.partition_name
, p_file => v_logfile );
v_sql := q'[
CREATE TABLE whouse1.sbsft_tmp1
TABLESPACE TSD_SPORTS_BET_FACT_TOTE
LOGGING
COMPRESS
PARALLEL ( DEGREE 32 INSTANCES 1 )
AS
SELECT *
FROM whouse2.sports_bet_fact_tote PARTITION ( ]’ || r_parts.partition_name || q'[ )]’;
BEGIN
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
prod_run.d_log.WRITE
( p_text => v_sql
, p_file => v_logfile );
END;
v_sql := ‘SELECT COUNT(1) FROM whouse2.sports_bet_fact_tote PARTITION ( ‘ || r_parts.partition_name || ‘)’;
EXECUTE IMMEDIATE v_sql INTO v_src_count;
v_sql := ‘SELECT COUNT(1) FROM whouse1.sbsft_tmp1’;
EXECUTE IMMEDIATE v_sql INTO v_trg_count;
IF v_src_count != v_trg_count THEN
prod_run.d_log.WRITE
( p_text => ‘ERROR : Partition : ‘ || UPPER(r_parts.partition_name) ||
‘ Source Count : ‘ || v_src_count ||
‘ Target Count : ‘ || v_trg_count
, p_file => v_logfile );
raise_application_error( -20001
, ‘ERROR : Partition : ‘ || UPPER(r_parts.partition_name) ||
‘ Source Count : ‘ || v_src_count ||
‘ Target Count : ‘ || v_trg_count
);
END IF;
prod_run.d_log.WRITE
( p_text => ‘Source Target Check Ok : Source = ‘ || v_src_count || ‘ : Target = ‘ || v_trg_count
, p_file => v_logfile );
prod_run.d_log.WRITE
( p_text => ‘Analyse temp table for partition ‘ || r_parts.partition_name
, p_file => v_logfile );
sys.dbms_stats.gather_table_stats (
ownname => ‘WHOUSE1’
, tabname => ‘SBSFT_TMP1’
, estimate_percent => sys.dbms_stats.auto_sample_size
, method_opt => ‘FOR ALL INDEXED COLUMNS SIZE AUTO ‘
, DEGREE => 4
, CASCADE => FALSE
, no_invalidate => FALSE);
prod_run.d_log.WRITE
( p_text => ‘Exchange temp table for partition ‘ || r_parts.partition_name
, p_file => v_logfile );
v_sql :=’ALTER TABLE whouse1.sports_bet_fact_tote EXCHANGE PARTITION ‘ || r_parts.partition_name || ‘ WITH TABLE whouse1.sbsft_tmp1 INCLUDING INDEXES’;
EXECUTE IMMEDIATE v_sql;
v_sql :=’ALTER TABLE whouse2.sports_bet_fact_tote TRUNCATE PARTITION ‘ || r_parts.partition_name;
–EXECUTE IMMEDIATE v_sql;
END LOOP;
END;