Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
临时表空间的主要作用:
- 索引create或rebuild
- Order by 或 group by
- Distinct 操作
- Union 或 intersect 或 minus
- Sort-merge joins
- analyze
一些关于TEMP Tablespace的查询及操作
--查看TEMP表空间大小
SELECT
FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY,
BYTES/1024/1024/1024 "DISK_SIZE(G)", MAXBYTES/1024/1024/1024 "MAX_SIZE(G)"
FROM
DBA_TEMP_FILES
WHERE
TABLESPACE_NAME IN ('TEMP');
--查看临时表文件大小和已使用空间
select t1."Tablespace" "Tablespace",
t1."Total (G)" "Total (G)",
nvl(t2."Used (G)", 0) "Used(G)",
t1."Total (G)" - nvl(t2."Used (G)", 0) "Free (G)"
from
(
select tablespace_name "Tablespace", to_char((sum(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"
from dba_temp_files
GROUP by tablespace_name
union
select tablespace_name "Tablespace", to_char((sum(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"
from dba_data_files
where tablespace_name like'TEMP%'
GROUP by tablespace_name
) t1,
(
SELECT tablespace, round(sum(blocks)*8/1024) "Used (G)" from v$sort_usage
GROUP BY tablespace
) t2
where t1."Tablespace"=t2.tablespace(+);
--查看当前临死表使用空间大小与正在占用临时表空间的sql语句
SELECT SESS.SID, SEGTYPE, BLOCKS * 8 / 1000 "MB", SQL_TEXT
FROM V$SORT_USAGE SORT, V$SESSION SESS, V$SQL SQL
WHERE SORT.SESSION_ADDR = SESS.SADDR
AND SQL.ADDRESS = SESS.SQL_ADDRESS
ORDER BY BLOCKS DESC;
--修改临时表空间大小
SELECT'ALTER database TEMPFILE ' || FILE_NAME || ' resize 100M ;'
FROM DBA_TEMP_FILES
ALTER database TEMPFILE +DG_DATA/EDWDB/TEMPFILE/temp.259.963681813 resize 100M ;
--创建新的临时表空间
SQL> CREATE temporary tablespace TEMP1 TEMPFILE '+DG_DATA/EDWDB/TEMPFILE/TEMP1_01.dbf' size 100M;
--将当前临时表空间指定为新的临时表空间
SQL> alter database default temporary tablespace TEMP1;