merge_partition

set echo on time on timing on spool 01_merge_partition.log

@/testdb/change/env/env_test.sql connect &V_testDATA_UN/&V_testDATA_PW

show user

select index_name from user_indexes where status='UNUSABLE'; select index_name , partition_name from user_ind_partitions where status !='USABLE';

set echo on termout on feedback 1 set timing on time on

alter session set sort_area_size=300000000;

whenever sqlerror exit;

------------------------------     TABLE: test_MOVEMENT     ------------------------------

alter table test_MOVEMENT merge partitions  part144, part145 into partition part145 tablespace TSP_L;

alter table test_MOVEMENT deallocate unused;

alter index test_MOVE_PK1 rebuild online tablespace IDX_L;

alter index test_MOVE_IDX1 rebuild PARTITION part145 TABLESPACE IDX_L;

alter index test_MOVE_IDX2 rebuild PARTITION part145 TABLESPACE IDX_L;

alter index test_MOVE_IDX3 rebuild PARTITION part145 TABLESPACE IDX_L;

alter index test_MOVE_IDX4 rebuild PARTITION part145 TABLESPACE IDX_L;

alter index test_MOVE_IDX5 rebuild PARTITION part145 TABLESPACE IDX_L;

------------------------------     TABLE: test_CONT_AUDIT_LOG     ------------------------------

alter table test_CONT_AUDIT_LOG merge partitions  part147, part148 into partition part148 tablespace TSP_L;

alter table test_CONT_AUDIT_LOG deallocate unused;

alter index test_CONT_AUDIT_LOG_PK rebuild online tablespace IDX_L;

------------------------------     TABLE: test_test     ------------------------------

alter table test_test merge partitions part144, part145 into partition part145 tablespace TSP_L;

alter table test_test deallocate unused;

alter index test_test_PK rebuild tablespace IDX_L;

alter index test_test_IDX5 rebuild tablespace IDX_L;

alter index test_test_IDX7 rebuild tablespace IDX_L;

alter index test_test_IDX1 rebuild partition part145 tablespace IDX_L;

alter index test_test_IDX2 rebuild partition part145 tablespace IDX_L;

alter index test_test_IDX3 rebuild partition part145 tablespace IDX_L;

alter index test_test_IDX4 rebuild partition part145 tablespace IDX_L;

alter index test_test_IDX6 rebuild partition part145 tablespace IDX_L;

 

------------------------------     TABLE: test_test     ------------------------------

alter table test_test merge partitions part150, part151 into partition part151 tablespace TSP_L;

alter table test_test deallocate unused;

alter index test_test_IDX1 rebuild partition part151 tablespace IDX_L;

alter index test_test_PK rebuild tablespace IDX_L;

------------------------------     TABLE: test_test     ------------------------------

alter table test_test merge partitions part144, part145 into partition part145 tablespace TSP_L;

alter table test_test deallocate unused;

alter index test_test_PK rebuild tablespace IDX_L;

alter index test_test_IDX1 rebuild partition part145 tablespace IDX_L;

alter index test_test_IDX2 rebuild partition part145 tablespace IDX_L;

alter index test_test_IDX3 rebuild partition part145 tablespace IDX_L;

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

select index_name , partition_name from user_ind_partitions where status !='USABLE';

select index_name from user_indexes where status='UNUSABLE';

select distinct status from user_indexes; select distinct status from user_ind_partitions;

spool off