ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

哈喽，大家好，这是博主的第一篇微博，博主是从事dba工作，记录一些平时处理的case，和大家一起分享，喜欢dba工作的一起互粉呀！
问题现象：
数据库alert日志中一直在报;
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Sat Nov 07 08:10:26 2020
Thread 1 advanced to log sequence 7210 (LGWR switch)
Mon Nov 09 06:32:39 2020
Archived Log entry 13211 added for thread 1 sequence 7300 ID 0x4132911 dest 1:
Mon Nov 09 07:09:53 2020
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
这套库是新上线的，原本以为是数据库大量的排序或者直接路径读等操作把临时表空间占满了，所以就直接扩了temp表空间，增加了4个数据文件，每个文件大小30GB，但是过了一阵还是继续报错，这就要深入查一下原因：
这里有博主整理的查询临时表空间的语句，供大家参考：
查看临时表空间：
set lines200 pagesize 2000
col file_name for a50
col name for a50
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;

查询临时表空间使用率：
SELECT TABLESPACE_NAME,round(TABLESPACE_SIZE/1024/1024/1024) TABLESPACE_SIZE_GB,
round(ALLOCATED_SPACE/1024/1024/1024) ALLOCATED_SPACE_GB,
round(FREE_SPACE/1024/1024/1024) FREE_SPACE_GB
FROM DBA_TEMP_FREE_SPACE;

set lines 200 pagesize 2000
col TABLESPACE_NAME for a20
SELECT TABLESPACE_NAME,round(TABLESPACE_SIZE/1024/1024/1024) TABLESPACE_SIZE_GB,
round(ALLOCATED_SPACE/1024/1024/1024) ALLOCATED_SPACE_GB,
round((TABLESPACE_SIZE-FREE_SPACE)/1024/1024/1024) USED_SPACE_GB,
round(FREE_SPACE/1024/1024/1024) FREE_SPACE_GB,
to_char((TABLESPACE_SIZE-FREE_SPACE)*100/TABLESPACE_SIZE,99.99)||’%'use
FROM DBA_TEMP_FREE_SPACE;

查看临时表空间占用：
当前会话：
SELECT S.sid || ‘,’ || S.serial# sid_serial,
S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
T.sqladdr address,
Q.hash_value,
Q.sql_text
FROM v$sor{t}_{u}sageT,v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address(+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

历史会话：
select to_char(sample_time,‘yyyymmdd hh24:mi:ss’),SESSION_ID || ‘,’ ||SESSION_serial# sid_serial,sql_id,round(TEMP_SPACE_ALLOCATED/1024/1024/1024) temp_GB
from gv$active_session_history
where to_char(sample_time,‘yyyymmdd hh24:mi:ss’) between ‘20201107 11:51:00’ and ‘20201107 11:51:50’
order by TEMP_SPACE_ALLOCATED;

经过查询发现是这条sql语句占用大量的temp表空间：
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的过程了：
Frequent ORA-1652 seen in the Enterprise Manager Cloud Control Repository Database (Doc ID 2686736.1)
原来是我们对新增的机器增加em监控导致的，因为em度量衡监控里面有一个关于备份度量衡的设置，我们选择将这个度量衡进行禁用设置，这样问题解决，这里mos也给了其他的处理方式，比如收集数据字典的统计信息等，因为生产数据库，未做验证。