查看当前临时表空间:
SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE--------------------------------------------------------------------------------
TEMP
SQL>
查看系统的临时表空间:
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP1
TEMP2
PROD2_IAS_TEMP
PROD1_IAS_TEMP
SQL>
查看临时表空间使用率:
SQL> SELECT A.tablespace_name tablespace,
D.mb_total,
SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts# = C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP1 4000 47.125 3952.875
TEMP2 4000 375 3625
SQL>