SELECT *
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr




SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';


SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS ;


SET LINESIZE 1200
COL NAME FOR A60
SELECT FILE# AS FILE_NUMBER
,NAME AS NAME
,CREATION_TIME AS CREATION_TIME
,BLOCK_SIZE AS BLOCK_SIZE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)"
,STATUS AS STATUS
,ENABLED AS ENABLED
FROM V$TEMPFILE;


SET LINESIZE 1200
COL TABLESPACE_NAME FOR A30
COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME AS TABLESPACE_NAME
,FILE_NAME AS FILE_NAME
,BLOCKS AS BLOCKS
,STATUS AS STATUS
,AUTOEXTENSIBLE AS AUTOEXTENSIBLE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,DECODE(MAXBYTES,BYTES/1024/1024/1024,
MAXBYTES/1024/1024/1024)
AS "MAX_SIZE(G)"
,INCREMENT_BY AS "INCREMENT_BY"
,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;


SELECT *
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;


SELECT se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
FROM v$sort_usage su,v$parameter p,v$session se,v$sql s
WHERE p.name = 'db_block_size'
AND su.session_addr = se.saddr
AND s.hash_value = su.sqlhash
AND s.address = su.sqladdr
ORDER BY se.username,se.sid;




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 temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used,0) * 100 / total,3) "Free percent"
FROM (SELECT tablespace_name,SUM(bytes_used) / 1024 / 1024 used
FROM V$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name,SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name

ALTER DATABASE TEMPFILE 'D:\NEWTEMP01.DBF' RESIZE 2G;

http://www.cnblogs.com/kerrycode/p/4006840.html