创建分区表的存储过程。
CREATE OR REPLACE PROCEDURE P_ALTER_PARTITIONKEY
IS
V_PARTNAME1 VARCHAR2(100);
V_PARTNAME2 VARCHAR2(100);
V_PARTNAME3 VARCHAR2(100);
V_PARTNAME4 VARCHAR2(100);
V_PARTTIME1 VARCHAR2(100);
V_PARTTIME2 VARCHAR2(100);
V_PARTTIME3 VARCHAR2(100);
V_PARTTIME4 VARCHAR2(100);
BEGIN
V_PARTNAME1:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'01';
V_PARTNAME2:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'02';
V_PARTNAME3:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'03';
V_PARTNAME4:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'04';
V_PARTTIME1:=TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'08';
V_PARTTIME2:=TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'15';
V_PARTTIME3:=TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'22';
V_PARTTIME4:=TO_CHAR(ADD_MONTHS(SYSDATE,2),'YYMM')||'01';
FOR V_ITME IN (SELECT DISTINCT TABLE_NAME FROM User_Tab_Partitions WHERE TABLE_name NOT LIKE '%BAK%' and TABLE_name not like '%$%')
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME1||' VALUES LESS THAN ('''||V_PARTTIME1||''')';
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME2||' VALUES LESS THAN ('''||V_PARTTIME2||''')';
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME3||' VALUES LESS THAN ('''||V_PARTTIME3||''')';
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME4||' VALUES LESS THAN ('''||V_PARTTIME4||''')';
EXCEPTION WHEN OTHERS THEN NULL;
END ;
END LOOP;
END P_ALTER_PARTITIONKEY;
例子:
CREATE OR REPLACE PROCEDURE P_ALTER_PARTITIONKEY
IS
V_PARTNAME1 VARCHAR2(100);
V_PARTNAME2 VARCHAR2(100);
V_PARTNAME3 VARCHAR2(100);
V_PARTNAME4 VARCHAR2(100);
V_PARTTIME1 VARCHAR2(100);
V_PARTTIME2 VARCHAR2(100);
V_PARTTIME3 VARCHAR2(100);
V_PARTTIME4 VARCHAR2(100);
BEGIN
V_PARTNAME1:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'01';
V_PARTNAME2:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'02';
V_PARTNAME3:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'03';
V_PARTNAME4:='P'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'04';
V_PARTTIME1:=TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'08';
V_PARTTIME2:=TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'15';
V_PARTTIME3:=TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYMM')||'22';
V_PARTTIME4:=TO_CHAR(ADD_MONTHS(SYSDATE,2),'YYMM')||'01';
FOR V_ITME IN (SELECT DISTINCT TABLE_NAME FROM User_Tab_Partitions WHERE TABLE_name NOT LIKE '%BAK%' and TABLE_name not like '%$%')
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME1||' VALUES LESS THAN ('''||V_PARTTIME1||''')';
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME2||' VALUES LESS THAN ('''||V_PARTTIME2||''')';
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME3||' VALUES LESS THAN ('''||V_PARTTIME3||''')';
EXECUTE IMMEDIATE 'ALTER TABLE '||V_ITME.TABLE_NAME||' ADD PARTITION '||V_PARTNAME4||' VALUES LESS THAN ('''||V_PARTTIME4||''')';
EXCEPTION WHEN OTHERS THEN NULL;
END ;
END LOOP;
END P_ALTER_PARTITIONKEY;
例子:
DECLARE
X NUMBER;
BEGIN
DBMS_JOB.SUBMIT
( job => X
,what => 'P_ALTER_PARTITIONKEY;'
,next_date => to_date('2015/12/31 1:00:00','yyyy/mm/dd hh24:mi:ss')
,interval => 'TRUNC(LAST_DAY(SYSDATE+1)) +1/24'
);
COMMIT;
END;
/
定义x变量,job 是获取job的id,what定义要执行的存储过程的名称, next_date 定义的是下一次执行的时间, interval 定义的是时间间隔
定义完成可以通过试图来查看定义的job
select * from dba_jobs;
X NUMBER;
BEGIN
DBMS_JOB.SUBMIT
( job => X
,what => 'P_ALTER_PARTITIONKEY;'
,next_date => to_date('2015/12/31 1:00:00','yyyy/mm/dd hh24:mi:ss')
,interval => 'TRUNC(LAST_DAY(SYSDATE+1)) +1/24'
);
COMMIT;
END;
/
定义x变量,job 是获取job的id,what定义要执行的存储过程的名称, next_date 定义的是下一次执行的时间, interval 定义的是时间间隔
定义完成可以通过试图来查看定义的job
select * from dba_jobs;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30345407/viewspace-1985110/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30345407/viewspace-1985110/