生产环境需要定期删除分区历史数据,自己写了一个,拿出来分享下吧。
1.创建调度任务字典表
-- Create table
create table SCHEDULER_CONF_DIC
(
OWNER_NAME VARCHAR2(40),
TABLE_NAME VARCHAR2(40),
TYPE VARCHAR2(30),
REMAIN_DAY NUMBER
);
-- Add comments to the table
comment on table SCHEDULER_CONF_DIC
is '调度任务配置字典表';
-- Add comments to the columns
comment on column SCHEDULER_CONF_DIC.OWNER_NAME
is '表的拥有者';
comment on column SCHEDULER_CONF_DIC.TABLE_NAME
is '表名称';
comment on column SCHEDULER_CONF_DIC.TYPE
is '类型';
comment on column SCHEDULER_CONF_DIC.REMAIN_DAY
is '数据保留日期';
2.创建LONG_TO_VARCHAR的fuction
CREATE OR REPLACE FUNCTION LONG_TO_VARCHAR(P_TABLE_OWNER IN ALL_TAB_PARTITIONS.TABLE_OWNER%TYPE,
P_TABLE_NAME IN ALL_TAB_PARTITIONS.TABLE_NAME%TYPE,
P_PARTITION_NAME IN ALL_TAB_PARTITIONS.PARTITION_NAME%TYPE)
RETURN VARCHAR2 AS
L_HIGH_VALUE LONG;
BEGIN
SELECT HIGH_VALUE
INTO L_HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER = P_TABLE_OWNER
AND TABLE_NAME = P_TABLE_NAME
AND PARTITION_NAME = P_PARTITION_NAME;
RETURN SUBSTR(L_HIGH_VALUE, 1, 4000);
END;
3.创建测试表
create table test_drop
(
logid NUMBER not null,
create_date DATE
)
PARTITION BY RANGE(create_date) --按照时间进行的范围分区
INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) --11g新特性 间隔分区
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
insert into test_drop values (1,to_date('2015-03-04 12:12:12','yyyy-mm-dd hh24:mi:ss'));
commit;
4.配置调度任务字典表
OWNER_NAME TABLE_NAME TYPE REMAIN_MON
1 SYS TEST_DROP DROP_PARTITION 1
5.创建存储过程
CREATE OR REPLACE PROCEDURE SCHEDULER_DROP_PARTITION IS
/*author:夜无伤
do:定期删除分区表数据
*/
V_SQL VARCHAR2(300);
V_TRUNC VARCHAR2(300);
V_DROP VARCHAR2(300);
V_FIRST_P VARCHAR2(100);
BEGIN
FOR V_SQL IN (SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM (SELECT T.TABLE_OWNER,
T.TABLE_NAME,
T.PARTITION_NAME,
SUBSTR(LONG_TO_VARCHAR(T.TABLE_OWNER,
T.TABLE_NAME,
T.PARTITION_NAME),
11,
10) HIGH_VALUE
FROM DBA_TAB_PARTITIONS T,
(SELECT OWNER_NAME, TABLE_NAME
FROM SCHEDULER_CONF_DIC
WHERE TYPE = 'DROP_PARTITION'
AND REMAIN_MON = 1) TT
WHERE T.TABLE_NAME = TT.TABLE_NAME
AND T.TABLE_OWNER = TT.OWNER_NAME)
WHERE HIGH_VALUE <
TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy-mm-dd')) LOOP
DBMS_OUTPUT.PUT_LINE(V_SQL.TABLE_NAME || V_SQL.PARTITION_NAME);
V_TRUNC := 'alter table ' || V_SQL.TABLE_OWNER || '.' ||
V_SQL.TABLE_NAME || ' truncate partition ' ||
V_SQL.PARTITION_NAME || '';
V_DROP := 'alter table ' || V_SQL.TABLE_OWNER || '.' ||
V_SQL.TABLE_NAME || ' drop partition ' ||
V_SQL.PARTITION_NAME || '';
--DBMS_OUTPUT.PUT_LINE(V_TRUNC);
--DBMS_OUTPUT.PUT_LINE(V_DROP);
--EXECUTE IMMEDIATE V_TRUNC;
--execute immediate v_drop;
SELECT PARTITION_NAME
INTO V_FIRST_P
FROM (SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS T,
(SELECT OWNER_NAME, TABLE_NAME
FROM SCHEDULER_CONF_DIC
WHERE TYPE = 'DROP_PARTITION'
AND REMAIN_MON = 1) TT
WHERE T.TABLE_NAME = TT.TABLE_NAME
AND T.TABLE_OWNER = TT.OWNER_NAME
AND T.PARTITION_POSITION = 1);
IF V_SQL.PARTITION_NAME = V_FIRST_P THEN
NULL;
DBMS_OUTPUT.PUT_LINE(V_FIRST_P);
DBMS_OUTPUT.PUT_LINE(V_TRUNC);
EXECUTE IMMEDIATE V_TRUNC;
ELSE
DBMS_OUTPUT.PUT_LINE(V_TRUNC);
DBMS_OUTPUT.PUT_LINE(V_DROP);
EXECUTE IMMEDIATE V_TRUNC;
EXECUTE IMMEDIATE V_DROP;
END IF;
END LOOP;
END;
6.创建调度任务
begin
dbms_scheduler.create_job (
job_name => 'SCHEDULER_DROP',
job_type => 'STORED_PROCEDURE',
job_action => 'SCHEDULER_DROP_PARTITION', --存储过程名
start_date => sysdate,
repeat_interval => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0', -- 按月,间隔为1个(月),每月1号,凌晨1点
comments => '定期删除分区表数据'
);
end;
--创建后如果是它生效,需要启用
-- job 启用
begin
dbms_scheduler.enable('SCHEDULER_DROP');
end;
7.查询job信息
-- job 查询
SELECT OWNER,
JOB_NAME,
STATE,
START_DATE,
REPEAT_INTERVAL,
LAST_START_DATE,
NEXT_RUN_DATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'SCHEDULER_DROP'
OWNER JOB_NAME STATE START_DATE REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE
1 SYS SCHEDULER_DROP SCHEDULED 23-4月 -15 04.07.25.000000 下午 +08:00 FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0 01-5月 -15 01.00.00.500000 上午 +08:00
-- 手动执行job
begin
dbms_scheduler.run_job('SCHEDULER_DROP',TRUE); -- true代表同步执行
end;
8.要停止job最好drop
-- 停止(不好用)
begin
dbms_scheduler.stop_job(job_name => 'SCHEDULER_DROP',force => TRUE);
end;
-- job 删除(对停job来说好用)
begin
dbms_scheduler.drop_job(job_name => 'SCHEDULER_DROP',force => TRUE);)
end;
1.创建调度任务字典表
-- Create table
create table SCHEDULER_CONF_DIC
(
OWNER_NAME VARCHAR2(40),
TABLE_NAME VARCHAR2(40),
TYPE VARCHAR2(30),
REMAIN_DAY NUMBER
);
-- Add comments to the table
comment on table SCHEDULER_CONF_DIC
is '调度任务配置字典表';
-- Add comments to the columns
comment on column SCHEDULER_CONF_DIC.OWNER_NAME
is '表的拥有者';
comment on column SCHEDULER_CONF_DIC.TABLE_NAME
is '表名称';
comment on column SCHEDULER_CONF_DIC.TYPE
is '类型';
comment on column SCHEDULER_CONF_DIC.REMAIN_DAY
is '数据保留日期';
2.创建LONG_TO_VARCHAR的fuction
CREATE OR REPLACE FUNCTION LONG_TO_VARCHAR(P_TABLE_OWNER IN ALL_TAB_PARTITIONS.TABLE_OWNER%TYPE,
P_TABLE_NAME IN ALL_TAB_PARTITIONS.TABLE_NAME%TYPE,
P_PARTITION_NAME IN ALL_TAB_PARTITIONS.PARTITION_NAME%TYPE)
RETURN VARCHAR2 AS
L_HIGH_VALUE LONG;
BEGIN
SELECT HIGH_VALUE
INTO L_HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER = P_TABLE_OWNER
AND TABLE_NAME = P_TABLE_NAME
AND PARTITION_NAME = P_PARTITION_NAME;
RETURN SUBSTR(L_HIGH_VALUE, 1, 4000);
END;
3.创建测试表
create table test_drop
(
logid NUMBER not null,
create_date DATE
)
PARTITION BY RANGE(create_date) --按照时间进行的范围分区
INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) --11g新特性 间隔分区
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
insert into test_drop values (1,to_date('2015-03-04 12:12:12','yyyy-mm-dd hh24:mi:ss'));
commit;
4.配置调度任务字典表
OWNER_NAME TABLE_NAME TYPE REMAIN_MON
1 SYS TEST_DROP DROP_PARTITION 1
5.创建存储过程
CREATE OR REPLACE PROCEDURE SCHEDULER_DROP_PARTITION IS
/*author:夜无伤
do:定期删除分区表数据
*/
V_SQL VARCHAR2(300);
V_TRUNC VARCHAR2(300);
V_DROP VARCHAR2(300);
V_FIRST_P VARCHAR2(100);
BEGIN
FOR V_SQL IN (SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM (SELECT T.TABLE_OWNER,
T.TABLE_NAME,
T.PARTITION_NAME,
SUBSTR(LONG_TO_VARCHAR(T.TABLE_OWNER,
T.TABLE_NAME,
T.PARTITION_NAME),
11,
10) HIGH_VALUE
FROM DBA_TAB_PARTITIONS T,
(SELECT OWNER_NAME, TABLE_NAME
FROM SCHEDULER_CONF_DIC
WHERE TYPE = 'DROP_PARTITION'
AND REMAIN_MON = 1) TT
WHERE T.TABLE_NAME = TT.TABLE_NAME
AND T.TABLE_OWNER = TT.OWNER_NAME)
WHERE HIGH_VALUE <
TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy-mm-dd')) LOOP
DBMS_OUTPUT.PUT_LINE(V_SQL.TABLE_NAME || V_SQL.PARTITION_NAME);
V_TRUNC := 'alter table ' || V_SQL.TABLE_OWNER || '.' ||
V_SQL.TABLE_NAME || ' truncate partition ' ||
V_SQL.PARTITION_NAME || '';
V_DROP := 'alter table ' || V_SQL.TABLE_OWNER || '.' ||
V_SQL.TABLE_NAME || ' drop partition ' ||
V_SQL.PARTITION_NAME || '';
--DBMS_OUTPUT.PUT_LINE(V_TRUNC);
--DBMS_OUTPUT.PUT_LINE(V_DROP);
--EXECUTE IMMEDIATE V_TRUNC;
--execute immediate v_drop;
SELECT PARTITION_NAME
INTO V_FIRST_P
FROM (SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS T,
(SELECT OWNER_NAME, TABLE_NAME
FROM SCHEDULER_CONF_DIC
WHERE TYPE = 'DROP_PARTITION'
AND REMAIN_MON = 1) TT
WHERE T.TABLE_NAME = TT.TABLE_NAME
AND T.TABLE_OWNER = TT.OWNER_NAME
AND T.PARTITION_POSITION = 1);
IF V_SQL.PARTITION_NAME = V_FIRST_P THEN
NULL;
DBMS_OUTPUT.PUT_LINE(V_FIRST_P);
DBMS_OUTPUT.PUT_LINE(V_TRUNC);
EXECUTE IMMEDIATE V_TRUNC;
ELSE
DBMS_OUTPUT.PUT_LINE(V_TRUNC);
DBMS_OUTPUT.PUT_LINE(V_DROP);
EXECUTE IMMEDIATE V_TRUNC;
EXECUTE IMMEDIATE V_DROP;
END IF;
END LOOP;
END;
6.创建调度任务
begin
dbms_scheduler.create_job (
job_name => 'SCHEDULER_DROP',
job_type => 'STORED_PROCEDURE',
job_action => 'SCHEDULER_DROP_PARTITION', --存储过程名
start_date => sysdate,
repeat_interval => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0', -- 按月,间隔为1个(月),每月1号,凌晨1点
comments => '定期删除分区表数据'
);
end;
--创建后如果是它生效,需要启用
-- job 启用
begin
dbms_scheduler.enable('SCHEDULER_DROP');
end;
7.查询job信息
-- job 查询
SELECT OWNER,
JOB_NAME,
STATE,
START_DATE,
REPEAT_INTERVAL,
LAST_START_DATE,
NEXT_RUN_DATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'SCHEDULER_DROP'
OWNER JOB_NAME STATE START_DATE REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE
1 SYS SCHEDULER_DROP SCHEDULED 23-4月 -15 04.07.25.000000 下午 +08:00 FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0 01-5月 -15 01.00.00.500000 上午 +08:00
-- 手动执行job
begin
dbms_scheduler.run_job('SCHEDULER_DROP',TRUE); -- true代表同步执行
end;
8.要停止job最好drop
-- 停止(不好用)
begin
dbms_scheduler.stop_job(job_name => 'SCHEDULER_DROP',force => TRUE);
end;
-- job 删除(对停job来说好用)
begin
dbms_scheduler.drop_job(job_name => 'SCHEDULER_DROP',force => TRUE);)
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29477587/viewspace-1593130/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29477587/viewspace-1593130/