Enterprise Manager (EM)监控消耗大量的TEMP空间

夹心饼2024

于 2023-05-06 11:21:21 发布

阅读量167

点赞数

分类专栏：数据库运维案例分享文章标签：数据库 oracle sql

本文链接：https://blog.csdn.net/bing_yuan/article/details/130522986

版权

数据库运维案例分享专栏收录该内容

90 篇文章 1 订阅

订阅专栏

问题描述：

最近一直从数据库告警日志出现TEMP表空间不足的情况，大概每隔15分钟就会提示一次。

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Sat Sep 17 00:26:22 2022
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Sat Sep 17 00:41:02 2022
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Sat Sep 17 00:56:01 2022
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Sat Sep 17 01:11:06 2022
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Sat Sep 17 01:26:01 2022
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Sat Sep 17 01:41:06 2022
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Sat Sep 17 01:56:01 2022
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

分析：
查询表空间的使用率如下，可以看到表空间基本已经全部被使用完。

SQL> select c.tablespace_name,
  2  to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,
  3  to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,
to_c  4  har(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,
  5  to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
  6  from (select tablespace_name,sum(bytes) bytes
  7  from dba_temp_files GROUP by tablespace_name) c,
  8  (select tablespace_name,sum(bytes_cached) bytes_used
  9  from v$temp_extent_pool GROUP by tablespace_name) d
 10  where c.tablespace_name = d.tablespace_name;

TABLESPACE_NAME                TOTAL_GB    FREE_GB     USE_GB      USE
------------------------------ ----------- ----------- ----------- -------
TEMP                                 6.000        .001       5.999  99.98%

查询占用TEMP表空间的SQL语句，可以看到是emagent_SQL_oracle_database模块发起的语句。
在这里插入图片描述

emagent_SQL_oracle_database

SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1

MOS找到相同的案例，按照如下提供的方式最后解决了问题。
EM 13c: After Upgrade to Enterprise Manager 13.2 Cloud Control, a Recurring Query in 11.2.0.4 Target Database (SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS) Takes Five Minutes, Using Huge Amount of Temp Space (Doc ID 2201982.1)

在这里插入图片描述