Oracle 表領域毎の容量、空き容量、使用率取得 グラフ付き

list_tablespaces.sql

-- #!/bin/sh
-- #@(#) \$Id list_tablespaces.sh, v1.0
-- #
-- # su - oracle
-- # export ORACLE_SID=xxxx
-- # export ORACLE_HOME=/opt/app/oracle/product/12.2.0
-- #
-- # ../sql/list_tablespaces.sh
-- #
-- #export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
-- ${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF
-- get tablespaces size, empty size, usage.
--
-- history
--   V$FILESTATを追加
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 300
SET PAGESIZE 50000
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL list_tablespaces_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL list_tablespaces_\&&_CONNECT_IDENTIFIER._\&&USER_\NAME&&SYSTEM_DATE..log
COLUMN TABLESPACE_NAME  FORMAT A30
COLUMN ALLOCATED        FORMAT 999,999,999,990 HEADING "ALLOCATED(MB)"
COLUMN USED             FORMAT 999,999,999,990 HEADING "USED(MB)"
COLUMN PER_USED         FORMAT           990.0 HEADING "USED(%)"
COLUMN FREE             FORMAT 999,999,999,990 HEADING "FREE(MB)"
COLUMN MAX_BYTES        FORMAT 999,999,999,990 HEADING "MAX(MB)"
COLUMN X                FORMAT A1
COLUMN COUNTS           FORMAT 999,990 HEADING 'Free|Frags'
COLUMN GRAPH            FORMAT A40
COLUMN PHYRDS           FORMAT 999,999,999,990 HEADING "RECORD READ"
COLUMN PHYWRTS          FORMAT 999,999,999,990 HEADING "RECORD WRITE"
COLUMN PHYBLKRD         FORMAT 999,999,999,990 HEADING "BLOCK READ"
COLUMN PHYBLKWRT        FORMAT 999,999,999,990 HEADING "BLOCK WRITE"
SET TIMING ON
SELECT
    A.TABLESPACE_NAME
  , A.ALLOCATED / 1024 / 1024 AS ALLOCATED
  , (A.ALLOCATED - A.FREE) / 1024 / 1024 AS USED
  , (A.ALLOCATED - A.FREE) / A.ALLOCATED * 100 AS PER_USED
  , A.FREE      / 1024 / 1024 AS FREE
  , A.MAX_BYTES / 1024 / 1024 AS MAX_BYTES
  , A.COUNTS
  , CASE
        WHEN A.COUNTS >= 2 THEN  -- 2以上は表領域の断片化(フラグメンテーション)発生
            'X'
    END AS X
  , NVL(RPAD(TRIM(RPAD
       (' ', 20 * ROUND((A.ALLOCATED - A.FREE) / A.ALLOCATED, 1) + 1, '■'))
     , 20, '□'), CASE
                      WHEN NP.VALUE = 'AL32UTF8' THEN
                          '□□□□□□□□□□□□□□□□□□□□'  -- 20
                      ELSE
                          '□□□□□□□□□□'
                  END) AS GRAPH
  , A.PHYRDS
  , A.PHYWRTS
  , A.PHYBLKRD
  , A.PHYBLKWRT 
FROM(
    SELECT
        TS.TABLESPACE_NAME
      ,     SUM(DF.BYTES)     AS ALLOCATED
      , NVL(SUM(FS.BYTES), 0) AS FREE
      , NVL(MAX(FS.BYTES), 0) AS MAX_BYTES
      , COUNT(FS.BYTES)       AS COUNTS
      , FT.PHYRDS
      , FT.PHYWRTS
      , FT.PHYBLKRD
      , FT.PHYBLKWRT 
    FROM DBA_TABLESPACES TS
    LEFT OUTER JOIN DBA_DATA_FILES DF
    ON  DF.TABLESPACE_NAME = TS.TABLESPACE_NAME
    LEFT OUTER JOIN V$FILESTAT FT
-- Windows ↑,  UNIX/Linux では ↓ を有効に
--  LEFT OUTER JOIN V\$FILESTAT FT 
    ON  FT.FILE# = DF.FILE_ID
    LEFT OUTER JOIN DBA_FREE_SPACE FS
    ON  FS.TABLESPACE_NAME = TS.TABLESPACE_NAME
    GROUP BY
        TS.TABLESPACE_NAME
      , FT.PHYRDS
      , FT.PHYWRTS
      , FT.PHYBLKRD
      , FT.PHYBLKWRT
    ) A
INNER JOIN V$NLS_PARAMETERS NP
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- INNER JOIN V\$NLS_PARAMETERS NP
ON  NP.PARAMETER = 'NLS_CHARACTERSET'
ORDER BY
    A.TABLESPACE_NAME ;
SPOOL OFF
SET TIMING OFF
COLUMN TABLESPACE_NAME  CLEAR
COLUMN ALLOCATED        CLEAR
COLUMN USED             CLEAR
COLUMN PER_USED         CLEAR
COLUMN FREE             CLEAR
COLUMN MAX_BYTES        CLEAR
COLUMN COUNTS           CLEAR
COLUMN X                CLEAR
COLUMN GRAPH            CLEAR
COLUMN PHYRDS           CLEAR
COLUMN PHYWRTS          CLEAR
COLUMN PHYBLKRD         CLEAR
COLUMN PHYBLKWRT        CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET TERMOUT ON
-- exit
-- EOF
-- #export NLS_LANG=en_US
-- # ---------------------- End of list_tablespaces.sh ------------------------------

参考
OTN-Japan Code Tips 表領域の使用率(グラフ表示) 2018年9月現在、OTN-Japan Code Tips のコードは削除されてしまいました(たぶん全部)。有益なコードがいくつもあったので復活して欲しいものです。>> 日本オラクル殿