ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle定时删除、增加表分区(TOAD,PL/SQL)(转)

Oracle定时删除、增加表分区(TOAD,PL/SQL)(转)

原创 Linux操作系统 作者:wdnmg 时间:2011-02-16 19:12:45 0 删除 编辑

 首先,建立分区表。

 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类型,能够获得一个文档句柄,通过此句柄能够实现一般的文档操作功能。

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/imasmallbird/archive/2008/11/20/3340547.aspx

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/70109/viewspace-687366/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 回收undo表空间
请登录后发表评论 登录
全部评论

注册时间:2011-01-31

  • 博文量
    53
  • 访问量
    145532