http://mxm910821.iteye.com/blog/1670560
//--------------------创建可分区的表--先创建分区到23点噢---------------------
create table TMC_CURRENT_PUBLISH_ALL_TEST
(
CYCLE DATE,
LOCATIONID INTEGER,
DIRECTION INTEGER,
SPEED INTEGER,
EVENT INTEGER,
FLOW INTEGER,
DENSITY INTEGER,
FLAG CHAR(1),
CELLULARTIME VARCHAR2(20),
FINISHTIME DATE,
SYS_ID NVARCHAR2(20)
)
partition by range(CYCLE)(
partition M2015060317 values less than (TO_DATE(' 2015-06-03 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace RTT_PARTITION_TS,
partition M2015060318 values less than (TO_DATE(' 2015-06-03 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace RTT_PARTITION_TS,
partition M2015060319 values less than (TO_DATE(' 2015-06-03 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace RTT_PARTITION_TS,
partition M2015060320 values less than (TO_DATE(' 2015-06-03 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace RTT_PARTITION_TS,
partition M2015060321 values less than (TO_DATE(' 2015-06-03 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace RTT_PARTITION_TS,
partition M2015060322 values less than (TO_DATE(' 2015-06-03 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace RTT_PARTITION_TS,
partition M2015060323 values less than (TO_DATE(' 2015-06-03 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace RTT_PARTITION_TS
);
====================================================================================
创建下一天的分区,每小时创建一次
====================================================================================
CREATE OR REPLACE PROCEDURE TSA_BUSINESS_ALL_ADDPART
AS
daysAmount NUMBER; --添加1天之后的分区
i number; --循环变量
v_time VARCHAR2(2); --01..23拼装格式
v_SqlExec VARCHAR2(1000); --DDL语句变量
v_PartName VARCHAR2(100);--创建分区的名称
v_PartDate VARCHAR2(100); --日期条件
v_err_num NUMBER; --ORA错误号
v_err_msg VARCHAR2(100); --错误描述
begin
daysAmount :=1;
i:=0;
for i in 0..23 loop
v_PartName:= 'M'|| to_char(sysdate + daysAmount,'YYYYMMDD');
if(i<10) then
v_time := '0'||i;
else
v_time :=i;
end if;
v_PartName:=v_PartName||v_time;
v_PartDate:= to_char(Trunc(SYSDATE+daysAmount)+i/24,'yyyy-mm-dd hh24:mi:ss');
dbms_output.put_line(v_PartName);
dbms_output.put_line(v_PartDate);
v_SqlExec:='alter table tsa_business_all add partition '|| v_PartName ||' values less than
(to_date(''' ||v_PartDate || ''',''SYYYY-MM-DD HH24:MI:SS''))tablespace RTT_PARTITION_TS';
execute immediate v_sqlexec;
commit;
dbms_output.put_line(v_SqlExec);
v_time :='';
v_PartName :='';
end loop;
EXCEPTION
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('TSA_BUSINESS_ALL_ADDPART执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);
END;
====================================================================================
删除三天以前的分区
====================================================================================
CREATE OR REPLACE PROCEDURE TSA_BUSINESS_ALL_DROPPART
AS
v_SqlExec VARCHAR2(2000); --DDL语句变量
--先查找DaysAmount之前的表分区
cursor cursor_part is
select partition_name from user_tab_partitions
WHERE table_name= 'TSA_BUSINESS_ALL' AND to_date(SUBSTR(partition_name,2,8),'YYYYMMDD HH24:MI')< sysdate-2
order by partition_name;
cursor_oldpart cursor_part%rowType;
begin
open cursor_part;
loop
fetch cursor_part into cursor_oldpart;
exit when cursor_part%notfound;
v_sqlexec:='ALTER TABLE TSA_BUSINESS_ALL DROP PARTITION '||cursor_oldpart.partition_name;
execute immediate v_sqlexec;
commit;
dbms_output.put_line(v_sqlexec);
end loop;
close cursor_part;
END;
====================================================================================
启动上述分区存储过程
====================================================================================
create or replace procedure TSA_BUSINESS_ALL_PRO_JOB as
job1 number; --每天22点创建分区
job2 number; --每天23点删除分区
v_err_num NUMBER; --ORA错误号
v_err_msg VARCHAR2(200); --错误描述
begin
dbms_job.submit(job1,'TSA_BUSINESS_ALL_ADDPART;',sysdate,'TRUNC(SYSDATE+1) + 22/24'); --每天22点执行
dbms_job.submit(job2,'TSA_BUSINESS_ALL_DROPPART;',sysdate,'TRUNC(SYSDATE+1) + 23/24'); --每天23点执行
--dbms_job.submit(job1,'TSA_BUSINESS_ALL_ADDPART;',sysdate,'trunc(sysdate,''mi'')+15/(24*60)'); --每隔15分钟执行
--dbms_job.submit(job2,'TSA_BUSINESS_ALL_DROPPART;',sysdate,'trunc(sysdate,''mi'')+15/(24*60)'); --每隔15分钟执行
commit;
dbms_job.run(job1);
dbms_job.run(job2);
dbms_output.put_line('SUCCESS!');
EXCEPTION
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line('TSA_BUSINESS_ALL_PRO_JOB执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);
end TSA_BUSINESS_ALL_PRO_JOB;
====================================================================================
删除启动的存储过程
====================================================================================
create or replace procedure TSA_BUSINESS_ALL_KILL_JOB
is
begin
dbms_job.remove(131);
dbms_job.remove(132);
dbms_job.remove(133);
dbms_job.remove(134);
dbms_job.remove(135);
dbms_job.remove(136);
dbms_job.remove(137);
dbms_job.remove(138);
dbms_job.remove(139);
dbms_job.remove(140);
dbms_job.remove(141);
end TSA_BUSINESS_ALL_KILL_JOB;