Oracle 存储情况查询
--Oracle存储情况查询:
1 SELECT a.tablespace_name "表空间名", 2 c.fileaddr "存储路径", 3 c.pyr "读/次数", 4 c.pbr "读(block)/次数", 5 c.pyw "写/次数", 6 c.pbw "写(block)/次数", 7 total / (1024 * 1024 * 1024) "表空间大小(G)", 8 free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 9 (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 10 round((total - free) / total, 4) * 100 "使用率 %" 11 FROM (SELECT tablespace_name, SUM(bytes) free 12 FROM dba_free_space 13 GROUP BY tablespace_name) a, 14 (SELECT tablespace_name, SUM(bytes) total 15 FROM dba_data_files 16 GROUP BY tablespace_name) b, 17 (select df.tablespace_name name,df.file_name fileaddr,f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw 18 from v$filestat f, dba_data_files DF 19 where f.file# = df.file_id 20 order by df.tablespace_name) c 21 WHERE a.tablespace_name = b.tablespace_name 22 AND b.tablespace_name=c.name 23 ORDER BY "使用率 %" DESC
查询结果: