oracle 调用分区函数,ORACLE定时任务调用存储过程动态为表添加分区

1、创建需要自动分区的表

CREATE TABLE TEST(M DATA)

PARTITION BY RANGE(M)

(

PARTITION TEST_PAR_99991212 VALUES LESS THAN(TO_DATE('12-12-9999','DD-MM-YYYY'))

);

说明：创建的表必须要有一个足够大的初始分区，因为后续增加分区是基于当前这个分区上面分裂而来的。

2、创建存储过程

CREATE OR REPLACE

PROCEDURE "BB"(TABLE_NAME IN VARCHAR2，BEFORE_DAY IN NUMBER,AFTER_DAY IN NUMBER) AS

PAR_NAME VARCHAR2(50);--要创建的分区名称

PAR_NAME2 VARCHAR2(400)

V_DAY DATE;--分区对应日期

PAR_NAME_MAX VARCHAR2(20);

PAR_EXISTS INTEGER;

v_high_value VARCHAR2(255);

v_partition_max_date date;

CURSOR C_CUR(BEFOREDAY NUMBER,AFTERDAY NUMBER) IS

SELECT (SYSDATE-BEFOREDAY)+ROWNUM-1 AS S_DATE FROM DUAL CONNECT BY ROWNUM <= TRUC((SYSDATE+AFTERDAY)-(SYSDATE-BEFOREDAY));

CURSOR C_CUR2(TABLENAME VARCHAR2) IS

SELECT utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp where utp.table_name = TABLENAME AND utp.high_value_length <> 8 order by utp.partition_position ASC;

BEGIN

PAR_NAME_MAX := CONCAT(TABLE_NAME,'_PAR_MAX');

OPEN C_CUR(BEFORE_DAY,AFTER_DAY);--打开游标

loop

FETCH C_CUR INTO V_DAY;

EXIT WHERE C_CUR%NOTFOUND;

--要创建的分区名称

PAR_NAME := CONCAT(TABLE_NAME,CONCAT('_PAR_',TO_CHAR(V_DAY,'YYYYMMDD')));

for utp in C_CUR2(TABLE_NAME) loop

v_high_value := substr(utp.high_value,11,10);

v_partition_max_date := to_date(v_high_value,'YYYY-MM-DD');

IF V_DAY + 1 < v_partition_max_date THEN

PAR_NAME_MAX := TABLE_NAME || '_PAR_' || TO_CHAR(v_partition_max_date-1,'yyyymmdd');

EXIT;

END IF;

END loop;

PAR_EXISTS := 0;

SELECT COUNT(*) INTO PAR_EXISTS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = TABLE_NAME AND PARTITION_NAME = PAR_NAME;

IF PAR_EXISTS=0 THEN

EXECUTE IMMEDIATE 'alter table '||TABLE_NAME||' split partition '||PAR_NAME_MAX||' AT (TO_DATE('''||TO_CHAR(V_DAY+1,'yyyymmdd')||''',''yyyymmdd'')) INTO (PARTITION '||PAR_NAME||',PARTITION '||PAR_NAME_MAX||') UPDATE GLOBAL INDEXES';

END IF;

END loop;

close C_CUR;

COMMIT;

END BB;

3、在oracle的命令界面执行下面操作，启动一个job(正式项目里最好使用spring的定时任务调用上面存储过程)

sql> variable job0712 number;

sql> begin

2 dbms_job.submit(:job0712,'BB;',SYSDATE,'SYSDATE+1/1440');

3 end;

4 /

SAL> begin

2 dbms_job.run(:job0712);

3 end;

4 /

如下图：

说明：

一些常用命令：

--给指定表添加分区

alter table TEST ADD PARTITION PART_AAA VALUES LESS THAN (TO_DATE('01-JUL-2009','DD-MON-YYYY'))

--分裂分区

altertable TEST2 split partition ORD_ACT_PART02 AT(TO_DATE('01-JUL-2009','DD-MON-YYYY')) INTO (PARTITION P1,PARTITIONORD_ACT_PART02);

--删除指定表的指定分区

ALTERTABLE TEST3 DROP PARTITION part_aaa;

--查看某张表所有分区信息

SELECT *FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TEST2'

--查看指定分区记录

SELECT *FROM TEST2 PARTITION (P1)

--查看定时任务job相关信息

SELECTjob,broken,what,interval,t.* from user_jobs t;

SELECTjob,t.next_sec,SYSDATE from user_jobs t; --对应broken为N的时候job才为运行状态

SELECT *from dba_jobs order by job;

--删除job

A、查找job对应的id

SELECTjob,SYSDATE from user_jobs t; B、根据查找到的job的id来删除指定的job

--在删除分区表的时候，用下面的语句，否则分区表的信息会放到oracle的垃圾站中去，会导致存储过程中判断分区是否存在的时候会有问题

DROPTABLE TEST2 PURGE

--下面的语句是清空oracle的垃圾站数据 PURGErecyclebin;

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。