20200217-Oracle临时表过多导致exp速度慢

问题现象：

客户反馈数据库日常备份速度越来越慢。

问题原因：

远程查看数据库大小只有5G ，备份却需要 5 小时以上。

SQL> Select sum(bytes)/1024/1024/1024 from dba_segments where owner= ’ CJC ’ ; 

在进行exp 备份时，先导出创建表的语句，在导出表数据，查看备份日志，还没有导出表数据就开始卡住了， 猜测表数据量不大，但是表数量很大 ，导致在 exp 一开始导出创建表语句时卡住。

最终查看到CJC 用户下存在67 万张临时表；

SQL> select count(*) from  user_tables where temporary='Y';
  COUNT(*)
----------
    673165

其中以 TEM_ 开头的临时表有62 万张，以 TMPTABSUBJ% 开头的有4 万多张；

SQL> select count(*) from user_tables where temporary='Y' and table_name like'TEM_%';
  COUNT(*)
----------
    623866

SQL> select count(*) from user_tables where temporary='Y' and table_name like'TMPTABSUBJ%';
  COUNT(*)
----------
     47899

其中 TEM_ 开头临时表都是在09-14 年产生的，平均每天产生 1 万张临时表， 15-16 年没有这种类型的临时表；

SQL> select * from (select to_char(created,'yyyymmdd'),count(*)
  from user_tables a,user_objects b
  where a.table_name=b.object_name
  and a.temporary='Y'
  and a.table_name like'TEM_%'
   group by to_char(created,'yyyymmdd')
order by 1 desc
 )
 where rownum<=1000;
TO_CHAR(CREATED,'YYYYMMDD')   COUNT(*)
--------------------------- ----------
20140920                           122
20140919                         12207
20140918                         11449
20140917                         10951
20140916                         15047
20140915                         18865
......
69 rows selected

其中 TMPTABSUBJ 开头临时表都是在09-13 年产生的， 14-16 年没有这种类型的临时表；

SQL> select * from (
  select to_char(created,'yyyymmdd'),count(*)
from user_tables a,user_objects b
  where a.table_name=b.object_name
  and a.temporary='Y'
 and a.table_name like'TMPTABSUBJ%'
 group by to_char(created,'yyyymmdd')
 order by 1 desc
 )
 where rownum<=1000;
TO_CHAR(CREATED,'YYYYMMDD')   COUNT(*)
--------------------------- ----------
20130930                           109
20130929                           133
20130928                            13
......
30 rows selected

估计是应用程序使用完临时后没有及时自动删除，导致临时表数据量越来越多，在研发出补丁清理临时表之前，可以先通过存储过程，自动删除几天前的临时表。

先备份用户下所有表， 然后通过下面的存储过程删除5天前产生的 TEM_ 开头和 TMPTABSUBJ% 开头的临时表；

解决方案

创建删除临时表的存储过程

CREATE   OR   REPLACE   PROCEDURE  DROP_TEMPTAB AS
   CURSOR  a IS
     select  table_name
       from  user_tables c ,  user_objects d
      where  c.table_name =  d.object_name
        and  c.temporary =   'Y'
        and   ( c.table_name like   'TEM_%'   or  c.table_name like   'TMPTABSUBJ%' )
        and  d.object_type =   'TABLE'
        and  d.temporary =   'Y'
        and  d.CREATED <   sysdate   -   5 ;
BEGIN
   FOR  i IN  a LOOP
     EXECUTE   IMMEDIATE   'drop table '   ||  i.table_name ;
   END   LOOP ;
END ;

添加JOB ，定期执行该存储过程，自动删除临时表， 每天 3 点执行 JOB ，每 2 天执行一次；

SQL> VARIABLE JOBNO NUMBER;
SQL> VARIABLE INSTNO NUMBER;
SQL> BEGIN
           SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
           DBMS_JOB.SUBMIT(:JOBNO,
                           'DROP_TEMPTAB; ',
                           TRUNC(SYSDATE) + 1 + 3 / 24,
                           'TRUNC(SYSDATE)+ 2 + 3 /24',
                           TRUE,
                           :INSTNO);
           COMMIT;
        END;
  /
PL/SQL procedure successfully completed

查看JOB 是否创建成功

SQL> select * from dba_jobs;

欢迎关注我的公众号:IT小Chen