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_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)
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- delete_partition.pl 刪除分割槽delete
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- ORACLE刪除-表分割槽和資料Oracle
- SQL Server表分割槽刪除詳情DSCCSQLServer
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- ct_deletepartition.pl 刪除電信分割槽delete
- 【實驗】【PARTITION】RANGE分割槽表刪除分割槽
- 利用排程任務定時刪除分割槽
- ORACLE 刪除無用分割槽Oracle
- 對刪除分割槽的分割槽表執行TSPITR
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- sql server針對表增加新的分割槽SQLServer
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- ORACLE分割槽表管理[轉]Oracle
- mysql 動態新建以及刪除分割槽表MySql
- 【實驗】【PARTITION】RANGE分割槽表增加分割槽
- 資料表分割槽分割與刪除歷史資料
- Oracle SQL調優之分割槽表OracleSQL
- 刪除LINUX分割槽Linux
- Linux 分割槽刪除Linux
- swap分割槽新增刪除
- 全面學習分割槽表及分割槽索引(8)--增加和收縮表分割槽索引
- oracle分割槽表和分割槽表exchangeOracle
- oracle分割槽表總結(轉)Oracle
- 【轉】Oracle分割槽表維護Oracle
- Oracle分割槽表基礎運維-07增加分割槽(4 RANGE_HASH)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(5RANGE_LIST)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(6RANGE_RANGE)Oracle運維
- Oracle使用聯機重定義來給表增加新列與分割槽Oracle
- Oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽表和非分割槽表exchangeOracle
- oracle刪除表欄位和oracle表增加欄位Oracle
- oracle實用sql(14)--查詢分割槽表的分割槽列和子分割槽列OracleSQL
- SQL Server表分割槽SQLServer
- SQL server 分割槽表SQLServer