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型別，能夠獲得一個檔案控制程式碼，透過此控制程式碼能夠實現一般的檔案操作功能。

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/imasmallbird/archive/2008/11/20/3340547.aspx

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70109/viewspace-687366/，如需轉載，請註明出處，否則將追究法律責任。