怎么解决Oracle临时表过多导致exp速度慢问题

这篇文章主要介绍“怎么解决Oracle临时表过多导致exp速度慢问题”，在日常操作中，相信很多人在怎么解决Oracle临时表过多导致exp速度慢问题问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么解决Oracle临时表过多导致exp速度慢问题”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

问题现象：

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

问题原因：

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

---5G

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(*)

  2  from user_tables a,user_objects b

  3  where a.table_name=b.object_name

  4  and a.temporary='Y'

  5  and a.table_name like'TEM_%'

  6  group by to_char(created,'yyyymmdd')

  7  order by 1 desc

  8  )

  9  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 (

  2  select to_char(created,'yyyymmdd'),count(*)

  3  from user_tables a,user_objects b

  4  where a.table_name=b.object_name

  5  and a.temporary='Y'

  6  and a.table_name like'TMPTABSUBJ%'

  7  group by to_char(created,'yyyymmdd')

  8  order by 1 desc

  9  )

 10  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>

SQL> BEGIN

  2         SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;

  3         DBMS_JOB.SUBMIT(:JOBNO,

  4                         'DROP_TEMPTAB; ',

  5                         TRUNC(SYSDATE) + 1 + 3 / 24,

  6                         'TRUNC(SYSDATE)+2+3/24',

  7                         TRUE,

  8                         :INSTNO);

  9         COMMIT;

 10       END;

 11  /

PL/SQL procedure successfully completed

查看JOB是否创建成功

SQL> select * from dba_jobs

到此，关于“怎么解决Oracle临时表过多导致exp速度慢问题”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！