请问用什么sql来监控temp表空间的利用情况，谢谢了

显示全部楼层 · 2007-10-20 08:38:44

千问 · 2007-10-20 08:38:44

你监控临时表空间？

千问 · 2007-10-20 08:38:44

SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype, contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr

千问 · 2007-10-20 08:38:44

是这样吗？
select free_blocks*8/1024 "freespace M",max_blocks*8/1024 "totalspace M" from v$sort_segment;

千问 · 2007-10-20 08:38:44

SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype, contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr
这是那百年的代码拉?

千问 · 2007-10-20 08:38:44

select *
from v$temp_space_header

千问 · 2007-10-20 08:38:44

SQL>select * from v$temp_space_header;
TABLESPACE_NAME
FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
------------------------------ ---------- ---------- ----------- ---------- ----------- ------------
TEMP
1371195904 45312467664896 57088
1
SQL> select * from dba_temp_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
BYTES BLOCKS STATUSRELATIVE_FNO AUT MAXBYTESMAXBLOCKS
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------
INCREMENT_BY USER_BYTES USER_BLOCKS
------------ ---------- -----------
D:ORACLEPRODUCT10.2.0ORADATAORCLTEMP01.DBF
1 TEMP
838860800 102400 AVAILABLE
1 YES 3.4360E+104194302
12800837812224102272
SQL> select 371195904/837812224*100||'%' used_space from dual;
USED_SPACE
-----------------------------------------
44.3053817271589486858573216520650813517%

QQ截图未命名.jpg (34.21 KB, 下载次数: 1)
下载附件
2008-6-12 10:36 上传

千问 · 2007-10-20 08:38:44

不错。楼上的办法挺好。

千问 · 2007-10-20 08:38:44

SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)", USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",AUTOEXTENSIBLE
FROM
(SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,
(SELECT FILE_ID,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,FILE_ID) E,
(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)
ORDER BY TABLESPACE_NAME,FILE_NAME;
这个也可以，我一直用这个 ^_^

千问 · 2007-10-20 08:38:44

原帖由 carcase 于 2008-6-12 11:08 发表

这个也可以，我一直用这个 ^_^
写成一句了