Oracle 计划任务批量清理临时表实例
昨天发现近一段时间，公司某oracle库数据泵方式备份比之前慢了很多，备份集大小并未增长太多。查看了下发现该用户下存在几十万张表。
一、问题分析
1.查看用户下面的表
select count(*) from user_tables
count(*)
-- ---------
1 754583
2.按表名查看用户下的表类型数量统计
select substr(table_name, 0, 4), count(*)
from user_tables
group by substr(table_name, 0, 4)
order by count(*) desc
SUBSTR(table_name, 0, 4) count(*)
---------------------------------------------------------------------
1 SUME 441718
2 TEMP 209421
3 SUBR 34659
4 HP_T 3103
5 TEPT 1932
6 CITE 163
3.查看表数量最多的分类的全名
select * from user_tables where table_name like 'SUME%'
表名可以看出后面全是随机数，所有表为空表。
二、确认及解决问题
1.确认
经过和开发确认后SUME、TEMP、SUBR这三类表是程序代码中创建并使用完临时表后没有清理操作，历史的不会再被复用。
2.解决过程
（1）程序代码最后添加清理会话创建的临时表操作。
（2）通过存储过程和JOB任务清理历史的临时表和程序补丁生效前产生的临时表。
（3）待问题解决后停用/清理JOB任务。
（2.1）存储过程
select min(created),max(created) from user_objects where object_type = 'TABLE' and object_name like 'TEMP%'
--可以先看下要清理的临时表的时间范围，看是否存在近期使用的。
--存储过程正文
create or replace procedure p_drop_temq is v_tab_name varchar(30);
cursor cur_drop_temq is select object_name from user_objects where object_type = 'TABLE' and （object_name like 'TEMP%' or object_name like 'SUME%' or object_name like 'SUBR%' ） and created < sysdate - 3; --为了防止影响现有用户使用，清理3天之前的临时表。建议先执行查询语句确认下要清理的表，执行前做好备份。
begin
open cur_drop_temq;
loop fetch cur_drop_temq into v_tab_name;
exit when cur_drop_temq%notfound;
execute immediate 'drop table ' || v_tab_name;
end loop;
close cur_drop_temq;
end;
（2.2）创建job定时任务 实现自动调用存储过程
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(JOB => job, /*自动生成JOB_ID*/
WHAT => 'P_DROP_TEMQ;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate, /*初次执行时间*/
INTERVAL => 'sysdate+ 1+2/(24)' /*每天凌晨2点*/);
commit;
end;
（2.3）通过查询系统表查看该job信息 select * from user_jobs ；
详细操作可参考文章：Oracle JOB详解
（3）JOB的停用/清理
删除JOB方法：注：BROKEN值如果为TRUE则为停用，FALSE为启用，默认FALSE。
begin
DBMS_JOB.BROKEN(JOB编号,TRUE);
dbms_job.remove(JOB编号);
commit;
end;
以下是只执行stop job的效果
begin
DBMS_JOB.BROKEN(83,TRUE);
--dbms_job.remove(JOB编号);
commit;
end;
如果需要同时删除几个job
begin
--DBMS_JOB.BROKEN(43,TRUE);
dbms_job.remove(43);
--DBMS_JOB.BROKEN(44,TRUE);
dbms_job.remove(44);
--DBMS_JOB.BROKEN(45,TRUE);
dbms_job.remove(45);
commit;
end;
