partition 이름과 해당 파티션별 사이즈 조회

select partition_name,sum(bytes)/1024/1024 from dba_extents where owner='KKPOS' and segment_name='TEST'
group by partition_name;



관련 트리거 disable
alter trigger t_TEST_1 Disable;
alter trigger t_TEST_2 Disable;



속도 를 위해서 nologging

alter index INDEX명 nologging;



스크립트 뽑아내기

------------------------------------------------------------------
set head off echo off feed off verify off lin 200 pages 0 trims on

spool rebuild_TEST.sql

select 'alter session set db_file_multiblock_read_count=256;' from dual
union all
select 'alter index '||INDEX_NAME||' rebuild partition '|| PARTITION_NAME||' parallel 16;'
from DBA_IND_PARTITIONS
where index_name in
(select index_name from dba_indexes
where table_name ='TEST' and owner ='TESTER')
;
spool off

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

set head off echo off feed off verify off lin 200 pages 0 trims on

spool rebuild_part_index_MWSASAHT.sql

select 'alter session set db_file_multiblock_read_count=256;' from dual
union all
select 'alter index '||INDEX_NAME||' rebuild partition '|| PARTITION_NAME||' parallel 16;'
from DBA_IND_PARTITIONS
where index_name in
(select index_name from dba_indexes
where table_name ='TEST' and owner ='TEST')
;
spool off

---------------------------------------------------------------------
set head off echo off feed off verify off lin 200 pages 0 trims on

spool rebuild_part_TEST.sql

select 'alter session set db_file_multiblock_read_count=256;' from dual
union all
select 'alter index '||INDEX_NAME||' rebuild partition '|| PARTITION_NAME||' parallel 16;'
from DBA_IND_PARTITIONS
where index_name in
(select index_name from dba_indexes
where table_name ='TEST' and owner ='TEST')
;
spool off


TEST

alter session set db_file_multiblock_read_count=256;
alter session set sort_area_retained_size=50000000;
alter session set sort_area_size=50000000;
alter index XIE1TEST rebuild partition TEST_P_2004 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2012 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2012 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2011 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2011 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2010 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2010 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2009 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2009 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2008 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2006 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2006 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2007 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2008 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2007 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_2_2005 parallel 16;
alter index XIE1TEST rebuild partition TEST_P_1_2005 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2004 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2012 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2012 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2011 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2011 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2010 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2010 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2009 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2009 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2008 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2006 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2006 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2007 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2008 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2007 parallel 16;
alter index XPKTEST rebuild partition TEST_P_2_2005 parallel 16;
alter index XPKTEST rebuild partition TEST_P_1_2005 parallel 16;

-----------------------------------------------------------------------
MWSASAHT

alter session set db_file_multiblock_read_count=256;
alter session set sort_area_retained_size=50000000;
alter session set sort_area_size=50000000;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2004 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2005 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2006 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2007 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2008 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2009 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2012 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2012 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2011 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2011 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2010 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_1_2010 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2009 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2008 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2007 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2006 parallel 16;
alter index XIE1MWSASAHT rebuild partition MWSASAHT_P_2_2005 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2004 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2005 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2005 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2006 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2007 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2008 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2012 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2012 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2011 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2011 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2010 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2010 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2009 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_1_2009 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2008 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2007 parallel 16;
alter index XIE2MWSASAHT rebuild partition MWSASAHT_P_2_2006 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2004 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2005 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2006 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2007 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2008 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2009 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2012 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2012 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2011 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2011 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2010 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_1_2010 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2009 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2008 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2007 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2006 parallel 16;
alter index XPKMWSASAHT rebuild partition MWSASAHT_P_2_2005 parallel 16;
-----------------------------------------------------------------------
MWWHSTDT

alter session set db_file_multiblock_read_count=256;
alter session set sort_area_retained_size=50000000;
alter session set sort_area_size=50000000;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2004 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2006 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2007 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2008 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2012 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2012 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2011 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2011 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2010 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2010 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2009 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2009 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2008 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2007 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2006 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_1_2005 parallel 16;
alter index XIE1MWWHSTDT rebuild partition MWWHSTDT_P_2_2005 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2004 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2006 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2007 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2008 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2012 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2012 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2011 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2011 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2010 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2010 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2009 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2009 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2008 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2007 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2006 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_1_2005 parallel 16;
alter index XPKMWWHSTDT rebuild partition MWWHSTDT_P_2_2005 parallel 16;
-----------------------------------------------------------------------
MWWHSTMT

alter session set db_file_multiblock_read_count=256;
alter session set sort_area_retained_size=50000000;
alter session set sort_area_size=50000000;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2004 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2009 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2009 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2008 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2006 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2006 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2007 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2008 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2007 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2012 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2012 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2011 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2011 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2010 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2010 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_2_2005 parallel 16;
alter index XIE1MWWHSTMT rebuild partition MWWHSTMT_P_1_2005 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2004 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2006 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2007 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2008 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2009 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2009 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2012 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2012 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2011 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2011 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2010 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2010 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2008 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2007 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2006 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_1_2005 parallel 16;
alter index XIE2MWWHSTMT rebuild partition MWWHSTMT_P_2_2005 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2004 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2008 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2009 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2012 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2012 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2011 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2011 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2010 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2010 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2009 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2008 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2007 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2005 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2006 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2007 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_2_2006 parallel 16;
alter index XIE3MWWHSTMT rebuild partition MWWHSTMT_P_1_2005 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2004 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2006 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2006 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2007 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2008 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2009 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2010 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2011 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2012 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2012 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2011 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2010 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2009 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2008 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2007 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_1_2005 parallel 16;
alter index XIE4MWWHSTMT rebuild partition MWWHSTMT_P_2_2005 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2004 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2012 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2012 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2011 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2011 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2010 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2010 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2005 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2005 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2006 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2006 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2007 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2007 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2008 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2008 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_1_2009 parallel 16;
alter index XPKMWWHSTMT rebuild partition MWWHSTMT_P_2_2009 parallel 16;
-----------------------------------------------------------------------



truncate 파일 추출 (사용자 정의)

truncate.sql

alter table TEST truncate partition TEST_P_1_2005;
alter table TEST truncate partition TEST_P_1_2006;
alter table TEST truncate partition TEST_P_1_2007;
alter table TEST truncate partition TEST_P_1_2008;
alter table TEST truncate partition TEST_P_1_2009;
alter table TEST truncate partition TEST_P_2004;
alter table TEST truncate partition TEST_P_2_2005;
alter table TEST truncate partition TEST_P_2_2006;
alter table TEST truncate partition TEST_P_2_2007;
alter table TEST truncate partition TEST_P_2_2008;

alter table MWSASAHT truncate partition MWSASAHT_P_1_2005;
alter table MWSASAHT truncate partition MWSASAHT_P_1_2006;
alter table MWSASAHT truncate partition MWSASAHT_P_1_2007;
alter table MWSASAHT truncate partition MWSASAHT_P_1_2008;
alter table MWSASAHT truncate partition MWSASAHT_P_1_2009;
alter table MWSASAHT truncate partition MWSASAHT_P_2004;
alter table MWSASAHT truncate partition MWSASAHT_P_2_2005;
alter table MWSASAHT truncate partition MWSASAHT_P_2_2006;
alter table MWSASAHT truncate partition MWSASAHT_P_2_2007;
alter table MWSASAHT truncate partition MWSASAHT_P_2_2008;

alter table MWWHSTDT truncate partition MWWHSTDT_P_1_2005;
alter table MWWHSTDT truncate partition MWWHSTDT_P_1_2006;
alter table MWWHSTDT truncate partition MWWHSTDT_P_1_2007;
alter table MWWHSTDT truncate partition MWWHSTDT_P_1_2008;
alter table MWWHSTDT truncate partition MWWHSTDT_P_1_2009;
alter table MWWHSTDT truncate partition MWWHSTDT_P_2004;
alter table MWWHSTDT truncate partition MWWHSTDT_P_2_2005;
alter table MWWHSTDT truncate partition MWWHSTDT_P_2_2006;
alter table MWWHSTDT truncate partition MWWHSTDT_P_2_2007;
alter table MWWHSTDT truncate partition MWWHSTDT_P_2_2008;

alter table MWWHSTMT truncate partition MWWHSTMT_P_1_2005;
alter table MWWHSTMT truncate partition MWWHSTMT_P_1_2006;
alter table MWWHSTMT truncate partition MWWHSTMT_P_1_2007;
alter table MWWHSTMT truncate partition MWWHSTMT_P_1_2008;
alter table MWWHSTMT truncate partition MWWHSTMT_P_1_2009;
alter table MWWHSTMT truncate partition MWWHSTMT_P_2004;
alter table MWWHSTMT truncate partition MWWHSTMT_P_2_2005;
alter table MWWHSTMT truncate partition MWWHSTMT_P_2_2006;
alter table MWWHSTMT truncate partition MWWHSTMT_P_2_2007;
alter table MWWHSTMT truncate partition MWWHSTMT_P_2_2008;

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

rebuild 실행


rebuild.sql
@rebuild_part_index_TEST.sql
@rebuild_part_index_MWWHSTDT.sql
@rebuild_part_index_MWSASAHT.sql
@rebuild_part_index_MWWHSTMT.sql
-----------------------------------------------------------------------


logging 모드로 변환



alter index XIE1TEST logging;
alter index XPKTEST logging;
alter index XIE1MWSASAHT logging;
alter index XIE2MWSASAHT logging;
alter index XPKMWSASAHT logging;
alter index XIE1MWWHSTDT logging;
alter index XPKMWWHSTDT logging;
alter index XIE1MWWHSTMT logging;
alter index XIE2MWWHSTMT logging;
alter index XIE3MWWHSTMT logging;
alter index XIE4MWWHSTMT logging;
alter index XPKMWWHSTMT logging;
-----------------------------------------------------------------------



인덱스 확인



select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE1TEST';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XPKTEST';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE1MWSASAHT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE2MWSASAHT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XPKMWSASAHT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE1MWWHSTDT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XPKMWWHSTDT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE1MWWHSTMT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE2MWWHSTMT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE3MWWHSTMT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XIE4MWWHSTMT';

select index_name, PARTITION_NAME, STATUS, TABLESPACE_NAME from user_ind_partitions
where index_name ='XPKMWWHSTMT';



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

트리거 enable



alter trigger t_TEST_d enable;
alter trigger t_TEST_i enable;
alter trigger t_mwsasaht_d enable;
alter trigger t_mwsasaht_i enable;
alter trigger t_mwsasaht_i enable;
alter trigger t_mwwmpmtt_i enable;

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

size확인

select partition_name,sum(bytes)/1024/1024 from dba_extents where owner='KKPOS' and segment_name='TEST'
group by partition_name;

select sum(bytes)/1024/1024 from dba_segments where segment_name='TEST';

select partition_name,sum(bytes)/1024/1024 from dba_extents where owner='KKPOS' and segment_name='MWSASAHT'
group by partition_name;

select sum(bytes)/1024/1024 from dba_segments where segment_name='MWSASAHT';

select partition_name,sum(bytes)/1024/1024 from dba_extents where owner='KKPOS' and segment_name='MWWHSTDT'
group by partition_name;

select sum(bytes)/1024/1024 from dba_segments where segment_name='MWWHSTDT';

select partition_name,sum(bytes)/1024/1024 from dba_extents where owner='KKPOS' and segment_name='MWWHSTMT'
group by partition_name;

select sum(bytes)/1024/1024 from dba_segments where segment_name='MWWHSTMT';

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