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