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.
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 200
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
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
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
    FROM DBA_TABLESPACES TS
    LEFT OUTER JOIN DBA_DATA_FILES DF
    ON  DF.TABLESPACE_NAME = TS.TABLESPACE_NAME
    LEFT OUTER JOIN DBA_FREE_SPACE FS
    ON  FS.TABLESPACE_NAME = TS.TABLESPACE_NAME
    GROUP BY
        TS.TABLESPACE_NAME
    ) A
-- INNER JOIN V$NLS_PARAMETERS NP
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- INNER JOIN V\$NLS_PARAMETERS NP
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
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 ------------------------------