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, visibility, 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/，如需轉載，請註明出處，否則將追究法律責任。

SPLIT PARTITION 正式指令碼

相關文章