Oracle日常问题
-临时表过多导致
exp速度慢
问题现象：
客户反馈数据库日常备份速度越来越慢。
问题原因：
远程查看数据库大小只有5G
，备份却需要
5
小时以上。
---5G
SQL> Select sum(bytes)/1024/1024/1024 from dba_segments where owner=
’
CJC
’
;
在进行exp
备份时，先导出创建表的语句，在导出表数据，查看备份日志，还没有导出表数据就开始卡住了，
猜测表数据量不大，但是表数量很大
，导致在
exp
一开始导出创建表语句时卡住。
最终查看到CJC
用户下存在67
万张临时表；
S
QL> 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;
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
