Oracle查看表空间大小和使用率
1. 全部表空间的大小
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------------- --------------------
UNDOTBS1 65
SYSAUX 520
USERS 6
SYSTEM 680
EXAMPLE 100
2. 空闲表空间大小
select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------------- --------------------
SYSAUX 34
UNDOTBS1 1
USERS 1
SYSTEM 4
EXAMPLE 22
3. 已使用空间可以这样计算
select a.tablespace_name, total, free, total-free as used from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
TABLESPACE_NAME TOTAL FREE USED
-------------------- ---------- ---------- ----------
SYSAUX 520 33.6875 486.3125
UNDOTBS1 65 1 64
USERS 6.25 1.25 5
SYSTEM 680 3.5 676.5
EXAMPLE 100 21.5625 78.4375
更具体的sql语句:
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;