維護的ORACLE 10G資料庫中分區表幾十上百個,當需要添加新分區的時候非常麻煩,就寫了下面兩個
簡易的SQL語句來生成批量添加分區的腳本,如在新增分區時想索引放在另外表空間,對每個索引要先
執行下面語句:
--ALTER INDEX IDX_TABXX MODIFY DEFAULT ATTRIBUTES TABLESPACE P_IND_03_P2011;
按天分區:
select 'alter table '
||'&OWNER_NAME'
||'.'
||'&TAB_NAME'
||' split partition '
||'&MAX_PARTNAME'
||' at ('
||''''
||to_char((to_date('&START_PART_DT','yyyymmdd')+level ),'yyyymmdd')
||''''
||') INTO (PARTITION '
||'&TAB_NAME'
||'_'
||to_char((to_date('&START_PART_DT','yyyymmdd')+level-1 ),'yyyymmdd')
||' tablespace '
||'&TBS_NAME'
||',partition '
||'&MAX_PARTNAME'
||' );'
from dual CONNECT BY LEVEL <=
(SELECT (TO_DATE('&END_DT','YYYYMMDD')+1 - TO_DATE('&START_PART_DT','YYYYMMDD')) AS CNT FROM DUAL)
按月分區:
select 'alter table '
||'&OWNER_NAME'
||'.'
||'&TAB_NAME'
||' split partition '
||'&MAX_PARTNAME'
||' at ('
||''''
||TO_CHAR(add_months(TO_DATE('&START_PART_DT', 'YYYYMMDD'), level),'YYYYMMDD')
||''''
||') INTO (PARTITION '
||'&TAB_NAME'
||'_'
||TO_CHAR(add_months(TO_DATE('&START_PART_DT', 'YYYYMMDD'), level-1),'YYYYMM')
||' tablespace '
||'&TBS_NAME'
||',partition '
||'&MAX_PARTNAME'
||' );'
from dual CONNECT BY LEVEL < 13