Create partition Table/Index：
1.
確認是否需要新增Table/Index Tablespace
2.
確認空間是否足夠
3.
Create Partition table
4.
Split
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 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
