[Oracle]全PDBの表領域使用率を一括で確認するSQL
マルチテナント環境で全PDBの表領域使用率を一括で表示するSQLです。
CDB$ROOTで実行すると全PDBの情報を表示します。
特定のPDB上で実行するとそのPDB上の表領域のみ表示します。
SQL
alter session set "_px_cdb_view_enabled"=false;
set lines 300
set pages 100
col pdb format a20
col tablespace_name format a20
col size(mb) format 999,999,999
col used(mb) format 999,999,999
col free(mb) format 999,999,999
col max(mb) format 999,999,999
col used(%) format 990.9
select
(select name from v$containers where con_id = df.con_id) as pdb, -- pdb名
tablespace_name, -- 表領域名
nvl(total_bytes/1024/1024,0) as "size(mb)", -- データファイルのサイズ
(total_bytes - nvl(free_total_bytes,0)) / 1024 / 1024 as "used(mb)", -- データ使用量
nvl(free_total_bytes/1024/1024,0) as "free(mb)", -- データファイルの未使用サイズ
nvl(max_bytes/1024/1024,0) as "max(mb)", -- データファイル拡張最大サイズ
round((total_bytes - nvl(free_total_bytes,0)) / max_bytes * 100,1) as "used(%)" -- 使用率(used(mb)/max(mb))
from
( select
con_id,
tablespace_name,
sum(bytes) total_bytes,
--autoextensible=noの場合 現在のサイズ(bytes)をmaxbytesに設定
sum(case autoextensible when upper('yes') then maxbytes else bytes end) max_bytes
from
cdb_data_files
group by
con_id,tablespace_name
) df
left outer join
( select
con_id,
tablespace_name free_tablespace_name,
sum(bytes) free_total_bytes
from
cdb_free_space
group by con_id,tablespace_name
) fs
on df.con_id = fs.con_id
and df.tablespace_name = fs.free_tablespace_name
where 1=1
-- and tablespace_name = upper('system')
order by pdb,tablespace_name
/
実行例
PDB TABLESPACE_NAME size(mb) used(mb) free(mb) max(mb) used(%)
-------------------- -------------------- ------------ ------------ ------------ ------------ -------
CDB$ROOT SYSAUX 1,650 1,204 446 32,768 3.7
CDB$ROOT SYSTEM 840 835 5 32,768 2.5
CDB$ROOT UNDOTBS1 525 40 485 32,768 0.1
CDB$ROOT UNDOTBS2 200 30 170 32,768 0.1
CDB$ROOT USERS 5 1 4 32,768 0.0
PDBHANPEN01 SYSAUX 205 184 21 32,768 0.6
PDBHANPEN01 SYSTEM 360 339 21 32,768 1.0
PDBHANPEN01 TBS01 100 1 99 1,024 0.1
PDBHANPEN01 UNDOTBS1 265 31 234 32,768 0.1
PDBHANPEN01 UNDO_2 265 229 36 32,768 0.7
PDBHANPEN01 USERS 199 177 22 32,768 0.5
11行が選択されました。
SQL>
1行目のalter session 文はCDBビューのパラレル実行での検索を無効化している隠しパラメーター設定です。
CDBビューは各PDB上のDBAビューが結合されたビューです。デフォルトでは各PDB上のDBAビューをパラレル実行で検索し結合します。CDBビューを含むクエリが複雑だったりすると「ORA-12850: 指定したすべてのインスタンスにスレーブを割当てできません: 2が必要で、0が割当て済です」で失敗してしまう場合があったため、パラレル実行を無効化して回避しています。
