Oracleデータベースで表領域の使用状況を確認するSQL文

概要
システム環境
Oracle表領域の使用状況を確認するSQL文
1. 全ての表領域を表示
2. 表領域名を指定して表示

概要

Oracleデータベースで表領域の使用状況※を確認するSQL文を記載する。
※表領域サイズ、使用サイズ、空きサイズ、使用率

システム環境

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.16.0.1.0

Oracle表領域の使用状況を確認するSQL文

全ての表領域を表示

SQL> select
  tablespace_name,
  nvl(TOTAL_BYTES / 1024 / 1024 ,0) as "SIZE[MB]",
  nvl((TOTAL_BYTES - FREE_BYTES) / 1024 / 1024,0) as "USED[MB]",
  nvl(FREE_BYTES / 1024 / 1024,0) as "FREE[MB]",
  round(nvl((TOTAL_BYTES - FREE_BYTES) / TOTAL_BYTES * 100,100),2) as "RATE[%]"
from
  ( select
      tablespace_name,
      sum(bytes) TOTAL_BYTES
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tbs_name,
      sum(bytes) FREE_BYTES
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tbs_name(+)
order by tablespace_name
/
TABLESPACE_NAME                  SIZE[MB]   USED[MB]   FREE[MB]    RATE[%]
------------------------------ ---------- ---------- ---------- ----------
DATA                                 1124   305.6875   818.3125       27.2
DBFS_DATA                             100    24.0625    75.9375      24.06
SAMPLESCHEMA                       204800          0          0        100
SYSAUX                             3420.5   1540.375   1880.125      45.03
SYSTEM                                971     962.25       8.75       99.1
UNDOTBS1                             1230     571.75     658.25      46.48
UNDO_7                             95.375         36     59.375      37.75
7 rows selected.
SQL>

表領域名を指定して表示

SQL> select
  tablespace_name,
  nvl(TOTAL_BYTES / 1024 / 1024 ,0) as "SIZE[MB]",
  nvl((TOTAL_BYTES - FREE_BYTES) / 1024 / 1024,0) as "USED[MB]",
  nvl(FREE_BYTES / 1024 / 1024,0) as "FREE[MB]",
  round(nvl((TOTAL_BYTES - FREE_BYTES) / TOTAL_BYTES * 100,100),2) as "RATE[%]"
from
  ( select
      tablespace_name,
      sum(bytes) TOTAL_BYTES
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tbs_name,
      sum(bytes) FREE_BYTES
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tbs_name(+)
and
  tablespace_name = '&tablespace_name'
/
Enter value for tablespace_name: SYSTEM
old  26:   tablespace_name = '&tablespace_name'
new  26:   tablespace_name = 'SYSTEM'
TABLESPACE_NAME                  SIZE[MB]   USED[MB]   FREE[MB]    RATE[%]
------------------------------ ---------- ---------- ---------- ----------
SYSTEM                                971     962.25       8.75       99.1
SQL>