[php]
/*临时转换表*/
create table SPLIT_TABLE
(
table_name VARCHAR2(30),
partition_name VARCHAR2(30),
high_value CLOB,
tablespace_name VARCHAR2(30),
p_date DATE
)；
/*生成的sql*/
create table SPLIT_TABLE_RSLT
(
table_name VARCHAR2(30),
partition_name VARCHAR2(30),
split_sql VARCHAR2(3000),
insert_date DATE,
app_date DATE,
id NUMBER
)
/*序列，保证sql执行顺序*/
— Create sequence
create sequence SEQ_SPLIT_TABLE_RSLT
minvalue 1
maxvalue 999999999999999999999999999
start with 61
increment by 1
cache 20;

/* 生成分区sql */
CREATE OR REPLACE PROCEDURE SPLIT_TABLE_SQL(V_TABLE_NAME IN VARCHAR2,
V_END_DATE IN VARCHAR2) AUTHID CURRENT_USER AS
N_MONTHS NUMBER;
V_SQL VARCHAR2(300);
V_SPLIT_SQL VARCHAR2(300);
V_MONTHS_DATE VARCHAR2(30);
V_MONTHS_DATE1 VARCHAR2(30);
V_PARTITION_NAME VARCHAR2(30);
V_PARTITION_NAME_MAX VARCHAR2(30);
D_PART_DATE DATE;
D_MAX_DATE DATE;
D_END_DATE DATE := TO_DATE(V_END_DATE || ‘ 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’);
V_SQL1 VARCHAR2(300);
BEGIN
/* 清空中间表，将新的分区信息载入该表 */
EXECUTE IMMEDIATE ‘TRUNCATE TABLE SPLIT_TABLE’;
INSERT INTO SPLIT_TABLE
(TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME)
SELECT T.TABLE_NAME,
T.PARTITION_NAME,
TO_LOB(T.HIGH_VALUE) HIGH_VALUE,
T.TABLESPACE_NAME
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME = V_TABLE_NAME;
COMMIT;
/* 将分区信息改写成日期 求分区信息中除了MAXVALUE 最大的日期*/
FOR C1 IN (SELECT TE.TABLE_NAME,
TE.PARTITION_NAME,
TO_CHAR(TE.HIGH_VALUE) HIGH_VALUE
FROM SPLIT_TABLE TE
WHERE TE.TABLE_NAME = V_TABLE_NAME
AND TO_CHAR(TE.HIGH_VALUE) <> ‘MAXVALUE’) LOOP
V_SQL1 := ‘select to_char( ‘ || C1.HIGH_VALUE || ‘ ) from dual’;
EXECUTE IMMEDIATE V_SQL1
INTO D_PART_DATE;
UPDATE SPLIT_TABLE SE
SET SE.P_DATE = D_PART_DATE
WHERE SE.TABLE_NAME = C1.TABLE_NAME
AND SE.PARTITION_NAME = C1.PARTITION_NAME;
COMMIT;
END LOOP;
EXECUTE IMMEDIATE ‘ SELECT MAX(LE.P_DATE) FROM SPLIT_TABLE LE GROUP BY LE.TABLE_NAME’
INTO D_MAX_DATE;
/*求分区信息中含有MAXVALUE PARTITION_NAME 的 */
EXECUTE IMMEDIATE ‘ SELECT TE.PARTITION_NAME FROM SPLIT_TABLE TE WHERE TO_CHAR(TE.HIGH_VALUE) = ‘ ||
CHR(39) || ‘MAXVALUE’ || CHR(39)
INTO V_PARTITION_NAME_MAX;
N_MONTHS := MONTHS_BETWEEN(D_END_DATE, D_MAX_DATE);
/* 拼接sql */
FOR I IN 1 .. N_MONTHS LOOP
V_MONTHS_DATE := TO_CHAR(ADD_MONTHS(D_MAX_DATE, I), ‘YYYY/MM’);
V_MONTHS_DATE1 := TO_CHAR(ADD_MONTHS(D_MAX_DATE, I – 1), ‘YYYYMM’);
V_PARTITION_NAME := ‘PART_’ || V_MONTHS_DATE1;
V_SPLIT_SQL := ‘ALTER TABLE ‘ || V_TABLE_NAME ||
‘ SPLIT PARTITION ‘ || V_PARTITION_NAME_MAX ||
‘ AT( TO_DATE( ‘ || CHR(39) || V_MONTHS_DATE ||
‘/01 00:00:00’ || CHR(39) || ‘, ‘ || CHR(39) ||
‘SYYYY/MM/DD HH24:MI:SS’ || CHR(39) || ‘,’ ||
CHR(39) || ‘NLS_CALENDAR=GREGORIAN’ || CHR(39) ||
‘) ) INTO(PARTITION ‘ || V_PARTITION_NAME ||
‘,PARTITION ‘ || V_PARTITION_NAME_MAX || ‘)’;
–DBMS_OUTPUT.PUT_LINE(V_SPLIT_SQL);
INSERT INTO SPLIT_TABLE_RSLT ST
(ST.ID,
ST.TABLE_NAME,
ST.PARTITION_NAME,
ST.SPLIT_SQL,
ST.INSERT_DATE)
VALUES
(SEQ_SPLIT_TABLE_RSLT.NEXTVAL,
V_TABLE_NAME,
V_PARTITION_NAME,
V_SPLIT_SQL,
SYSDATE);
COMMIT;
END LOOP;
END;

/*执行拆分脚本*/
CREATE OR REPLACE PROCEDURE EXEC_SPLIT_TABLE(V_TABLE_NAME IN VARCHAR2,
V_END_DATE IN VARCHAR2)
AUTHID CURRENT_USER AS
V_PROGRAM VARCHAR2(30) := ‘拆分分区’;
V_START_TIME DATE := SYSDATE;
V_TABLE_NAME1 VARCHAR2(30);
V_PARTITION_NAME VARCHAR2(30);
BEGIN
DELETE SPLIT_TABLE_RSLT ST WHERE ST.TABLE_NAME = V_TABLE_NAME;
SPLIT_TABLE_SQL(V_TABLE_NAME, V_END_DATE);
FOR C1 IN (SELECT ET.TABLE_NAME,
ET.PARTITION_NAME,
ET.SPLIT_SQL,
ET.INSERT_DATE,
ET.APP_DATE
FROM SPLIT_TABLE_RSLT ET
WHERE ET.TABLE_NAME = V_TABLE_NAME
ORDER BY ET.ID) LOOP
V_START_TIME := SYSDATE;
V_TABLE_NAME1 := C1.TABLE_NAME;
V_PARTITION_NAME := C1.PARTITION_NAME;
SAVE_ERROR_INFO(SQLCODE,
‘表:’ || C1.TABLE_NAME || ‘,分区名:’ || C1.PARTITION_NAME ||
‘开始执行！当前时间:’ ||
TO_CHAR(V_START_TIME, ‘YYYY-MM-DD HH24:MI:SS’),
V_PROGRAM);
COMMIT;

EXECUTE IMMEDIATE C1.SPLIT_SQL;

UPDATE SPLIT_TABLE_RSLT TET
SET TET.APP_DATE = SYSDATE
WHERE TET.TABLE_NAME = C1.TABLE_NAME
AND TET.PARTITION_NAME = C1.PARTITION_NAME;
SAVE_ERROR_INFO(SQLCODE,
‘表:’ || C1.TABLE_NAME || ‘,分区名:’ || C1.PARTITION_NAME ||
‘统计完成,耗时’ ||
TO_CHAR(ROUND(TO_NUMBER(SYSDATE – V_START_TIME) * 1440 * 60,
1),
‘fm9999990.0’) || ‘秒！’,
V_PROGRAM);
COMMIT;

END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
SAVE_ERROR_INFO(SQLCODE,
‘表:’ || V_TABLE_NAME1 || ‘,分区名:’ || V_PARTITION_NAME ||
‘,错误信息:’ || SQLERRM,
V_PROGRAM);
COMMIT;

END;
[/php]