Oracle数据库查看表空间使用率

12C之前或NON-CDB模式的数据库查看表空间使用率

set linesize 160
set pagesize 999
col TABLESPACE_NAME for a18
col TBS_TOTAL_MB for 9999999
col TBS_USED_MB for 9999999
col TBS_FREE_MB for 9999999
col TBS_RATE for a11           
col EXTEND_MAX_MB for 9999999999999
col EXTEND_FREE_MB for 9999999999999
col EXTEND_RATE for a11
select a.tablespace_name,
      round(current_size / 1024 / 1024, 1) TBS_TOTAL_MB,
      round((current_size - b.free_bytes) / 1024 / 1024, 1) TBS_USED_MB,
      round(b.free_bytes / 1024 / 1024, 1) TBS_FREE_MB,
      round(((current_size - b.free_bytes) / current_size) * 100, 1) || '%' TBS_RATE,
      round(a.max_size / 1024 / 1024, 1) EXTEND_MAX_MB,
      round((a.max_size - (current_size - b.free_bytes)) / 1024 / 1024, 1) EXTEND_FREE_MB,
      round(((current_size - b.free_bytes) / a.max_size) * 100, 1) || '%' "EXTEND_RATE"
  from (select tablespace_name,
              sum(ddf.bytes) current_size,
              sum(case
                    when ddf.autoextensible = 'YES' THEN
                      DDF.MAXBYTES
                    ELSE
                      DDF.BYTES
                  END) max_size
          from dba_data_files ddf
        group by tablespace_name
        union
        select tablespace_name,
              sum(ddf.bytes) current_size,
              sum(case
                    when ddf.autoextensible = 'YES' THEN
                      DDF.MAXBYTES
                    ELSE
                      DDF.BYTES
                  END) max_size
          from dba_temp_files ddf
        group by tablespace_name) a,
      (select dfs.tablespace_name, sum(dfs.bytes) free_bytes
          from dba_free_space dfs
        group by dfs.tablespace_name
        union
        select tfs.tablespace_name, sum(tfs.BYTES_FREE) free_bytes
          from v$TEMP_SPACE_HEADER tfs
        group by tfs.tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by 8;

主要观察EXTEND_RATE列，表示最大可扩展已使用率。

若为CDB模式数据库，可通过一下语句查询所有CDB和PDB的表空间使用率

set line 200 pages 999
column name for a10
column tablespace_name for a15
column "MAXSIZE (MB)" format 9,999,990.00
column "ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "PERC_USED" format 99.00
select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/(1024*1024) "MAXSIZE (MB)", nvl(a.physical_bytes,0)/(1024*1024) "ALLOC (MB)" ,nvl(b.tot_used,0)/(1024*1024) "USED (MB)" ,round((nvl(b.tot_used,0)/a.physical_bytes)*100,2) "USED%",round((nvl(b.tot_used,0)/a.bytes_alloc)*100,2) "PERC_USED"
from
(select con_id,tablespace_name, sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from cdb_data_files group by con_id,tablespace_name ) a,
(select con_id,tablespace_name, sum(bytes) tot_used from cdb_segments group by con_id,tablespace_name ) b,
(select name,con_id from v$containers) c
where a.con_id=b.con_id and a.con_id = c.con_id and a.tablespace_name = b.tablespace_name (+)
order by 1,3;