临时表空间
SET LINESIZE 160
COL TABLESPACE_NAME FOR A30
SELECT TABLESPACE_NAME AS TABLESPACE_NAME
,SUM(BYTES)/1024/1024/1024 AS "TBS_SIZE(G)"
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME;
临时表空间文件
SET LINESIZE 160
COL TABLESPACE_NAME FOR A30
COL FILE_NAME FOR A50
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, 0, 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;
临时表空间情况
SYS@edw1> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
-------------------- ------------- ------------ ----------- -----------
TEMP 8 2350848 54784 2296064
EDWTMP 10 35318400 124032 35194368
谁在使用临时表空间:
Col PROGRAM for a20
Col MACHINE for a20
col username for a15
SELECT se.username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr
order by 7,1;
临时表空间上正在执行哪些SQL
Col tablespace name for a20
SELECT se.username
,se.sid
,se.serial#
,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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26474945/viewspace-1681051/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26474945/viewspace-1681051/