怎么解決Oracle臨時表過多導致exp速度慢問題

這篇文章主要介紹“怎么解決Oracle臨時表過多導致exp速度慢問題”，在日常操作中，相信很多人在怎么解決Oracle臨時表過多導致exp速度慢問題問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么解決Oracle臨時表過多導致exp速度慢問題”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

問題現(xiàn)象：

客戶反饋數(shù)據(jù)庫日常備份速度越來越慢。

問題原因：

遠程查看數(shù)據(jù)庫大小只有5G，備份卻需要5小時以上。

---5G

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

在進行exp備份時，先導出創(chuàng)建表的語句，在導出表數(shù)據(jù)，查看備份日志，還沒有導出表數(shù)據(jù)就開始卡住了，猜測表數(shù)據(jù)量不大，但是表數(shù)量很大，導致在exp一開始導出創(chuàng)建表語句時卡住。

最終查看到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

估計是應用程序使用完臨時后沒有及時自動刪除，導致臨時表數(shù)據(jù)量越來越多，在研發(fā)出補丁清理臨時表之前，可以先通過存儲過程，自動刪除幾天前的臨時表。

先備份用戶下所有表，然后通過下面的存儲過程刪除5天前產生的TEM_開頭和TMPTABSUBJ%開頭的臨時表；

---創(chuàng)建刪除臨時表的存儲過程

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，定期執(zhí)行該存儲過程，自動刪除臨時表， 每天3點執(zhí)行JOB，每2天執(zhí)行一次；

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是否創(chuàng)建成功

SQL> select * from dba_jobs

到此，關于“怎么解決Oracle臨時表過多導致exp速度慢問題”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關知識，請繼續(xù)關注億速云網站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>