Toad中oracle创建表分区,Oracle定时删除、增加表分区（TOAD,PL/SQL）

今日完成了这个任务，特做总结

首先，建立分区表。

CREATE TABLE MALS_NM_CPU_INFO_T_NEW

(

HOST_IP        VARCHAR2(40 BYTE)              NOT NULL,

HOST_NAME      VARCHAR2(15 BYTE)              NOT NULL,

COMMIT_TM      VARCHAR2(14 BYTE)              NOT NULL,

SYS_ID         VARCHAR2(32 BYTE)              NOT NULL,

CPU_FREQUENCY  NUMBER(10),

CPU_LOAD       NUMBER(3)

)

PARTITION BY RANGE (COMMIT_TM)  --以COMMIT_TM字段做分区条件(PARTITION P_NM_CPU_INFO_20081120 VALUES LESS THAN ('20081121'));

若是已经建立了表，但是却没有分区，就只能根据已经有的表新建分区表了，如下：

CREATE TABLE MALS_NM_CPU_INFO_T_NEW

(

HOST_IP        VARCHAR2(40 BYTE)              NOT NULL,

HOST_NAME      VARCHAR2(15 BYTE)              NOT NULL,

COMMIT_TM      VARCHAR2(14 BYTE)              NOT NULL,

SYS_ID         VARCHAR2(32 BYTE)              NOT NULL,

CPU_FREQUENCY  NUMBER(10),

CPU_LOAD       NUMBER(3)

)

PARTITION BY RANGE (COMMIT_TM)

(PARTITION P_NM_CPU_INFO_20081120 VALUES LESS THAN ('20081121'));

insert into MALS_NM_CPU_INFO_T_NEW select * from MALS_NM_CPU_INFO_T;

rename MALS_NM_CPU_INFO_T to MALS_NM_CPU_INFO_T_old;

rename MALS_NM_CPU_INFO_T_new to MALS_NM_CPU_INFO_T;

create index nm_cpu_info_index on MALS_NM_CPU_INFO_T(Commit_Tm);  --建立局部分区索引，默认与当前表分区，在一个表空间中

此时建立完表，在TOAD中会在表旁边看见分区图标

其次，建立增加分区的存储过程。

CREATE OR REPLACE PROCEDURE MALS_NM_ADD_PARTITION_PROC(

partNum  NUMBER,  --添加分区的个数TableSpaceName VARCHAR2 --分区名) AS

v_SqlExec VARCHAR2(2000); --DDL语句变量v_PartDate VARCHAR2(20); --创建分区的日期(YYYYMMDD)

v_PartDate1 VARCHAR2(20); --创建分区的日期(YYYYMMDD)

v_err_num  NUMBER;  --ORA错误号v_err_msg  VARCHAR2(100); --错误描述

v_cpu_info_max VARCHAR2(20); --mals_nm_cpu_info_t表分区的最大日期

BEGIN

--查询已创建mals_nm_cpu_info_t表分区的最大日期--P_NM_CPU_INFO_20080221

select max(SUBSTR(partition_name,15,8)) into v_cpu_info_max

from user_tab_partitions

WHERE table_name=UPPER('MALS_NM_CPU_INFO_T');

FOR i IN 1..partNum LOOP

--创建mals_nm_cpu_info_t表分区IF v_cpu_info_max

v_PartDate:=to_char(SYSDATE+i,'YYYYMMDD');

v_PartDate1:=to_char(SYSDATE+i+1,'YYYYMMDD');

v_SqlExec:='ALTER TABLE MALS_NM_CPU_INFO_T ADD PARTITION P_NM_CPU_INFO_' || v_PartDate ||

' values less than(''' || v_PartDate1 || '000000'') TABLESPACE ' ||

TableSpaceName;

dbms_output.put_line('创建mals_nm_cpu_info_t表分区' || i || '='||v_SqlExec);

DBMS_Utility.Exec_DDL_Statement(v_SqlExec);

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

v_err_num := SQLCODE;

v_err_msg := SUBSTR(SQLERRM, 1, 100);

dbms_output.put_line('MALS_NM_ADD_PARTITION_PROC执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);

END MALS_NM_ADD_PARTITION_PROC;

再次，建立删除分区的存储过程

CREATE OR REPLACE PROCEDURE MALS_NM_DROP_PARTITION_PROC(

beforeDays NUMBER  --删除多少天前的分区)

As

v_SqlExec  VARCHAR2(2000); --DDL语句变量v_err_num  NUMBER;  --ORA错误号v_err_msg  VARCHAR2(100); --错误描述

--查找beforeDays天前存在的mals_nm_cpu_info_t表分区cursor cursor_cpu_info_part is

select partition_name from user_tab_partitions

WHERE table_name=UPPER('MALS_NM_CPU_INFO_T')

AND SUBSTR(partition_name,15,8)

ORDER BY partition_name;

record_cpu_info_oldpart cursor_cpu_info_part%rowType;

BEGIN

open cursor_cpu_info_part;

loop

fetch cursor_cpu_info_part into record_cpu_info_oldpart;

exit when cursor_cpu_info_part%notfound;

--删除mals_nm_cpu_info_t表分区v_SqlExec:='ALTER TABLE MALS_NM_CPU_INFO_T DROP PARTITION ' ||

record_cpu_info_oldpart.partition_name;

dbms_output.put_line('删除mals_nm_cpu_info_t表分区='||v_SqlExec);

DBMS_Utility.Exec_DDL_Statement(v_SqlExec);

end loop;

close cursor_cpu_info_part;

EXCEPTION

WHEN OTHERS THEN

v_err_num := SQLCODE;

v_err_msg := SUBSTR(SQLERRM, 1, 100);

dbms_output.put_line('MALS_NM_DROP_PARTITION_PROC执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);

END MALS_NM_DROP_PARTITION_PROC;

然后，建立两个执行增加、删除分区的存储过程

执行增加的存储过程：

CREATE OR REPLACE PROCEDURE MALS_NM_EXEC_ADD_PROC AS

v_err_num  NUMBER;  --ORA错误号v_err_msg  VARCHAR2(100); --错误描述BEGIN

--10代表创建10天的分区，tablespace代表表空间名MALS_NM_ADD_PARTITION_PROC(10,'tablespace');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

v_err_num := SQLCODE;

v_err_msg := SUBSTR(SQLERRM, 1, 100);

dbms_output.put_line('MALS_NM_DROP_PARTITION_PROC执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);

END MALS_NM_EXEC_ADD_PROC;

执行删除的存储过程:

CREATE OR REPLACE PROCEDURE MALS_NM_EXEC_DROP_PROC AS

v_err_num  NUMBER;  --ORA错误号v_err_msg  VARCHAR2(100); --错误描述BEGIN

--删除3个月前的数据100代表100天MALS_NM_DROP_PARTITION_PROC(100);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

v_err_num := SQLCODE;

v_err_msg := SUBSTR(SQLERRM, 1, 100);

dbms_output.put_line('MALS_NM_DROP_PARTITION_PROC执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);

END MALS_NM_EXEC_DROP_PROC;

建立完以上四个存储过程若是发现某个存储过程前有一个红叉，刚说明存在语法错误，此时若是在PL/SQL中可以进入该存储过程，按F8进行编译，控制台会看见相应错误信息的提示，若是在TOAD中可以在存储过程中点击右键，选择“compile”即可开始调试。

最后，建立JOB定时执行存储过程

create or replace procedure MALS_NM_JOBS_PROC as

job1 number;  --每天1点创建分区job2 number;  --每天3点删除分区v_err_num  NUMBER;  --ORA错误号v_err_msg  VARCHAR2(100); --错误描述

begin

dbms_job.submit(job1,'MALS_NM_EXEC_ADD_PROC;',sysdate,'TRUNC(SYSDATE+1) + (1*60)/(24*60)');

dbms_job.submit(job2,'MALS_NM_EXEC_DROP_PROC;',sysdate,'TRUNC(SYSDATE+1) + (3*60)/(24*60)');

commit;

EXCEPTION

WHEN OTHERS THEN

v_err_num := SQLCODE;

v_err_msg := SUBSTR(SQLERRM, 1, 100);

dbms_output.put_line('MALS_NM_DROP_PARTITION_PROC执行出现异常,错误码='|| v_err_num || '错误描述=' || v_err_msg);

end   MALS_NM_JOBS_PROC;

建立完这个存储过程后，就可以运行这个存储过程以达到我们定时删除、增加分区的功能

在运行了这个存储过程之后，如果一切正常在PL/SQL或TOAD下刷新，则会在JOB中发现两个新建立的JOB。

若是没有发现新的JOB则说明建立的某个存储过程虽然编译通过但是在执行中仍有问题，此时我们就要对建立的

存储过程进行DEBUG了(在PL/SQL中)。

首先，在存储过程中感觉存在问题的地方打上断点(CTRL＋B);

其次，右键单击存储过程，选择“Test”;

然后，在弹出的Test windows窗口的下方，为我们的存储过程，填写所需要的参数;

再次，点击窗口左上角的图标，或直接按F9，进入调试窗口，开始调试。

还要记得，要调试，必须具有调试的权限，否则需要用管理员用户赋予权限

grant Debug Connect Session to user

为了在系统测试或现网环境中确认我们的代码是否正常工作时，还可以记录log。plsql提供了一个utl_file包，通过定义utl_file包中的file_type类型，能够获得一个文档句柄，通过此句柄能够实现一般的文档操作功能。