我想很少人去烏這塊，一般來說主機cpu,ram夠強 跟本用不到temp tablespace
1.查詢目前使用狀況，找了幾台千萬的主機一試，結果都是0..
Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" , d.file_name "Datafile name", round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB", round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB", round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2) "Free KB", round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB",0 "Fragmentation Index"
from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id;
2.它說這招可以查單獨session誰用的量 和語法
不過用下去也查不到啥有用的資訊
select b.tablespace,b.segfile#,b.segblk#,b.blocks,b.blocks*32/1024/1024,
a.sid,a.serial#,a.username,a.osuser,a.status,c.sql_text,b.contents
from v$session a,v$sort_usage b,v$sql c
where a.saddr=b.session_addr
and a.sql_address=c.address(+)
order by b.blocks desc;
留言列表
