【oracle】临时表空间满

【环境描述】

公司内部使用的Oracle系统，版本是Oracle11g

【报错信息】

$ oerr ora 01652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

【报错原因】
应用程序执行的SQL产生了占用大量资源的排序操作。

【解决方法】
1） 给临时表空间添加临时数据文件扩容
# 查看临时表空间

select * from dba_tablespaces where contents='TEMPORARY';

# 查看临时表空间使用率

-- 查看表空间使用情况
select a.tablespace_name,a.max_mb,a.current_mb,b.free_mb,(a.current_mb-b.free_mb) used_mb,round((b.free_mb/a.current_mb)*100,3)||'%' free_pct
from
-- 空间最大容量、使用量
(select tablespace_name,sum(maxbytes)/1048576 MAX_MB, sum(bytes)/1048576 CURRENT_MB from dba_temp_files group by tablespace_name ) a,
-- 表空间剩余空间
(select tablespace_name,sum(free_space)/1048576 FREE_MB from DBA_TEMP_FREE_SPACE group by tablespace_name ) b 
where a.tablespace_name=b.tablespace_name
and a.tablespace_name='TEMP';

#查看临时表空间的数据文件信息

select * from DBA_TEMP_FREE_SPACE where tablespace_name='TEMP';
 
select * from dba_temp_files;

# 添加临时数据文件

alter tablespace TEMP add tempfile '/U01/app/oracle/oradata/testdb/temp05.dbf' size 10M autoextend on next 10M maxsize UNLIMITED;

# 对临时表空间进行shrink space

 alter tablespace temp shrink space;  -- 5个临时表空间数据文件，其中4个已经分配32G，另一个是新添加的，执行shrink space用时284/60=4.8分钟