2014/07/24

Published 7月 24, 2014 by

Create partition Table/Index

Create partition Table/Index
1.      確認是否需要新增Table/Index Tablespace
2.      確認空間是否足夠
3.      Create Partition table
4.      Split Partition
5.      Create Partition Index
6.      確認建立的Partition(找出最大的Partition)

一、產生新的 Table/index Tablespace語法(自行確認是否要新增)
CREATE TABLESPACE STGC5_TRG_2015Q1 DATAFILE
  '/rptdata1/c5prpt/STGC5_TRG_2015Q1_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M,
  '/rptdata2/c5prpt/STGC5_TRG_2015Q1_02.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M,
  '/rptdata3/c5prpt/STGC5_TRG_2015Q1_03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M,
  '/rptdata4/c5prpt/STGC5_TRG_2015Q1_04.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 32000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

二、確認Tablespace空間大小:
select t.tablespace_name ,avg(d.bytes/d.maxbytes)*100 as Used_Rate,sum(d.maxbytes)/1024/1024/1024 as max_g,sum(d.bytes)/1024/1024/1024 as Used_g,
       (sum(d.maxbytes)-sum(d.bytes))/1024/1024/1024 as free_space_g
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 group by t.tablespace_name
ORDER BY Free_space_g

三、增加Partition Table:

1.日期格式:
select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' ADD PARTITION P2015Q1 VALUES LESS THAN (''20150401 000000'') TABLESPACE DWC5_TBL;'
from dba_tab_partitions t where table_owner='DWC5' AND PARTITION_NAME='P2014Q1';

結果:
ALTER TABLE DWC5.CUT_TIME_WIP ADD PARTITION P2015Q1 VALUES LESS THAN ('20150401 000000') TABLESPACE DWC5_TBL;


2.時間格式:
select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' ADD PARTITION P2015Q1 VALUES LESS THAN TO_DATE('' 2015-04-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'') TABLESPACE DWC5_TBL;'
from dba_tab_partitions t where table_owner='DWC5' AND PARTITION_NAME='P2014Q1';

結果:
ALTER TABLE DWC5.CUT_TIME_WIP ADD PARTITION P2015Q1 VALUES LESS THAN TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') TABLESPACE DWC5_TBL;

四、Split Partition
SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' SPLIT PARTITION CTHI_MAXVALUE (TO DATE(''20170101 000000'') INTO (PARTITION '
||PARTITION_NAME|| ', PARTITION CTHI_MAXVALUE TABLESPACE) ' ||TABLESPACE_NAME|| ';' from dba_tab_partitions t
where table_owner='C5PROD' AND PARTITION_NAME LIKE '%2015Q4';

結果:
ALTER TABLE C5PROD.FWAMTCOMPTRACKHISTORY SPLIT PARTITION CTHI_MAXVALUE (TO DATE('20170101 000000') INTO (PARTITION CTHI_2015Q4, PARTITION CTHI_MAXVALUE) TABLESPACE C5_PTT_CTHI10;

五、增加Partition Index
每季增加:
SELECT * FROM (SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD PARTITION P2015Q4 TABLESPACE= ' || TABLESPACE_NAME || ';'  from dba_segments 
where tablespace_name='DWC5_IDX' AND PARTITION_NAME='P2014Q4');

結果:
ALTER INDEX DWC5.XPKCUT_TIME_WIP_P REBUILD PARTITION P2015Q4 TABLESPACE= DWC5_IDX;

一次找出一整年(Partition table要先建立好)
SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD PARTITION '||PARTITION_NAME|| ' TABLESPACE '||TABLESPACE_NAME|| ';'
FROM ALL_IND_PARTITIONS WHERE INDEX_OWNER='STGC5' AND PARTITION_NAME LIKE '%2014Q%';

結果:
ALTER INDEX IDX_COMPDLTYPETIME_600A REBUILD PARTITION P2014Q1 TABLESPACE STGC5_TRG_IDX_2014Q1;

六、找出最大的Partition
SELECT INDEX_NAME,max(PARTITION_NAME) FROM ALL_IND_PARTITIONS WHERE INDEX_OWNER='STGC5'  group by INDEX_NAME order by max(PARTITION_NAME);


參考資料:

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm