查询默认临时表空间名称：

select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

查询临时表空间大小以及使用情况：

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

增加临时表空间（）

ALTER TABLESPACE TEMP（临时表空间名称） ADD TEMPFILE 'temp2.dbf'（自己命名） SIZE 10M（或者G等其他单位，但10这个位置必须是整数）;

oracle临时表空间扩容
查看临时表空间

select *
from (Select a.tablespace_name,
to_char(a.bytes / 1024 / 1024, ‘99,999.999’) total_bytes,
to_char(b.bytes / 1024 / 1024, ‘99,999.999’) free_bytes,
to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
‘99,999.999’) use_bytes,
to_char((1 - b.bytes / a.bytes) * 100, ‘99.99’) || ‘%’ use
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes / 1024 / 1024, ‘99,999.999’) total_bytes,
to_char((c.bytes - d.bytes_used) / 1024 / 1024, ‘99,999.999’) free_bytes,
to_char(d.bytes_used / 1024 / 1024, ‘99,999.999’) use_bytes,
to_char(d.bytes_used * 100 / c.bytes, ‘99.99’) || ‘%’ use
from (select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) c,
(select tablespace_name, sum(bytes_cached) bytes_used
from v$temp_extent_pool
group by tablespace_name) d
where c.tablespace_name = d.tablespace_name)
order by tablespace_name ;

查看临时表分区
SELECT *
FROM (SELECT USERNAME,
SESSION_ADDR,
SQL_ID,
CONTENTS,
SEGTYPE,
BLOCKS * 8 / 1024 / 1024 GB
FROM V$SORT_USAGE
ORDER BY BLOCKS DESC)
WHERE ROWNUM <= 200;

查看表空间使用率

SELECT a.tablespace_name “表空间名称”,

total / (1024 * 1024) “表空间大小(M)”,

free / (1024 * 1024) “表空间剩余大小(M)”,

(total - free) / (1024 * 1024 ) “表空间使用大小(M)”,

total / (1024 * 1024 * 1024) “表空间大小(G)”,

free / (1024 * 1024 * 1024) “表空间剩余大小(G)”,

(total - free) / (1024 * 1024 * 1024) “表空间使用大小(G)”,

round((total - free) / total, 4) * 100 “使用率 %”

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

1.查看临时表空间的数据文件及存放路径
select tablespace_name,
file_name,
user_bytes/bytes,
bytes / 1024 / 1024 “file_size(M)”,
autoextensible
from dba_temp_files t
where t.TABLESPACE_NAME = ‘TEMP’;

登录数据库服务器，执行扩容语句，如下

SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 4G;