oracle 监控表空间使用情况常用SQL整理

查看表空间使用情况

select upper(f.tablespace_name) "表空间名",

d.tot_grootte_mb "表空间大小(m)",

d.tot_grootte_mb - f.total_bytes "已使用空间(m)",

to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,

2),

'990.99') "使用比",

f.total_bytes "空闲空间(m)",

f.max_bytes "最大块(m)"

from (select tablespace_name,

round(sum(bytes) / (1024 * 1024), 2) total_bytes,

round(max(bytes) / (1024 * 1024), 2) max_bytes

from sys.dba_free_space

group by tablespace_name) f,

(select dd.tablespace_name,

round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb

from sys.dba_data_files dd

group by dd.tablespace_name) d

where d.tablespace_name = f.tablespace_name

order by 4 desc;

查看表空间中每个表的大小

select segment_name, isize || 'M'

from (select segment_name, sum(bytes) / 1024 / 1024 isize

from dba_segments

where tablespace_name = 'SNBST_HIST'

AND segment_type = 'TABLE'

GROUP BY segment_name)

order by isize desc;

查看表空间物理文件使用大小

SELECT B.TABLESPACE_NAME 表空间,

B.FILE_NAME 物理文件名,

B.BYTES / 1024 / 1024 大小M,

(B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,

SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5)||'%' 利用率

FROM DBA_FREE_SPACE A, DBA_DATA_FILES B

WHERE A.FILE_ID = B.FILE_ID

GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES

ORDER BY B.TABLESPACE_NAME;