SPLIT PARTITION 正式脚本

周末在生产环境进行了SPLIT PARTITION 操作，较之前的SPLIT 实验相比，对脚本进行了相应的修改。
首先先将需要SPLIT分区的进行统计信息收集，然后将数据进行备份，再把索引变为UNUSABLE，然后进行SPLIT，再重将索引进行重建，检查是否有失效的索引（这一步很关键），最后重新收集统计信息。
和之前脚本相比，最大的区别是没有在SPLIT的时候直接更新索引，而是将操作分开来进行，先把索引变为UNUSABLE，然后SPLIT，这样做首先会加快SPLIT的进度，然后再重建索引的时候也相对可控，可以监控到具体在重建哪个索引。


EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'A',TABNAME => 'B',PARTNAME =>'ALL_FY',DEGREE => 16,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',GRANULARITY => 'PARTITION',CASCADE => TRUE);

--TABLE B
CREATE TABLE C.B_2015
AS 
SELECT *
FROM A.B
WHERE CREATION_DATE>=TO_DATE('2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS');

----------------------make global indexes unusable-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' unusable;' FROM dba_indexes
where table_name='B' and owner = 'A' and partitioned = 'NO';

----------------------split partition-------------------
ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2015,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2016,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2017,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2018,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2019,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2020,partition ALL_FY)
PARALLEL 16;

----------------------rebuild global partitioned and non-partitioned indexes-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' rebuild parallel 16;' FROM (
select distinct owner, index_name  , partitioned, visi bi lity, locality, status from  
(
        select * from 
        (
                select a.owner , a.index_name, a.partitioned, a.visibility, b.locality, c.partition_name,
                        case when (a.partitioned = 'YES' and d.status is not null) then d.status 
                        when (a.partitioned = 'YES' and c.status is not null) then c.status  
                        else a.status end status
                from dba_indexes a, dba_part_indexes b, dba_ind_partitions c, dba_ind_subpartitions d
                        where a.index_name = b.index_name(+)
                        and a.owner = b.owner(+)
                        and a.index_name = c.index_name (+)
                        and a.owner = c.index_owner (+)
                        and a.owner = d.index_owner (+)
                        and a.index_name = d.index_name (+)
                        and a.table_name = 'B'  
                        and (a.partitioned = 'NO')
        ) where status = 'UNUSABLE'
)
)
;

----------------------rebuild global partitioned and non-partitioned indexes-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' rebuild partition '|| partition_name ||' parallel 16;' FROM 
(
        select * from 
        (
                select a.owner , a.index_name, a.partitioned, a.visibility, b.locality, c.partition_name,
                        case when (a.partitioned = 'YES' and d.status is not null) then d.status 
                        when (a.partitioned = 'YES' and c.status is not null) then c.status  
                        else a.status end status
                from dba_indexes a, dba_part_indexes b, dba_ind_partitions c, dba_ind_subpartitions d
                        where a.index_name = b.index_name(+)
                        and a.owner = b.owner(+)
                        and a.index_name = c.index_name (+)
                        and a.owner = c.index_owner (+)
                        and a.owner = d.index_owner (+)
                        and a.index_name = d.index_name (+)
                        and a.table_name = 'B'  
                        and (a.partitioned = 'YES')
        ) where status = 'UNUSABLE'
)
;

----------------------change the index to no parallel-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' PARALLEL 1;' FROM dba_indexes
where partitioned ='NO' and table_name='B' and owner='A';

----------------------check index-------------------
select * FROM dba_indexes
where partitioned ='NO' and table_name='B' 
---and owner='A' 
and status !='VALID';

----------------------gather stats-------------------
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'A',TABNAME => 'B',DEGREE => 16,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'ALL',CASCADE => TRUE);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26084062/viewspace-1590375/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26084062/viewspace-1590375/