查询mysql临时表空间_查看临时表空间使用情况

查看临时表空间使用情况[@more@]

表空间使用情况:

select

a.tablespace_name

,a.total_G

,b.fee_G

,(a.total_G-b.fee_G)/a.total_G as "used%"

from

(select

a.tablespace_name

,sum(a.bytes)/1024/1024/1024 As total_G

from dba_data_files a

group by

a.tablespace_name

) a

left join (select

a.tablespace_name

,sum(a.bytes)/1024/1024/1024 as fee_G

from dba_free_space a

group by

a.tablespace_name

) b

on a.tablespace_name = b.tablespace_name

order by 4 desc

临时表空间使用情况:

Select

f.tablespace_name

,sum(f.bytes_free + f.bytes_used)

/1024/1024/1024 "total GB"

,sum((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0))

/1024/1024/1024 "Free GB"

,sum(nvl(p.bytes_used, 0))

/1024/1024/1024 "Used GB"

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

group by

f.tablespace_name

具体到某个SID临时表空间使用情况

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