表領域表示 - ぐま@Wiki

【Oracle】-SQL-表領域表示

set line 1000
COL TABLESPACE_NAME FORMAT A15
COL USED_MB         FORMAT 999,990
COL MAX_MB          FORMAT 999,999,990
COL FREE_MB         FORMAT 999,990
COL RATE            FORMAT A29
COL LOG             FORMAT A3
COL STATUS          FORMAT A5
COL CONTENTS        FORMAT A5
COL INITIAL_EXTENT  FORMAT 9,999,999,990
COL NEXT_EXTENT     FORMAT 9,999,999,990
COL MIN_EXTENTS     FORMAT 990
COL MAX_EXTENTS     FORMAT 9,999,999,990
COL PINC            FORMAT 990
COL EXT_MGR         FORMAT A4
COL ALCT_TYPE       FORMAT A20
COL IEXT_KB         FORMAT 9,999,990
COL NEXT_KB         FORMAT 9,999,990
COL MIN_EXTENTS     FORMAT 990
SELECT
    TABLESPACE_NAME,
    RATE,
    USED_BYTES  / 1024 / 1024 AS USED_MB,
    MAX_BYTES   / 1024 / 1024 AS MAX_MB,
    FREE_BYTES  / 1024 / 1024 AS FREE_MB,
    DECODE(STATUS, 'ONLINE', 'ON', 'OFFLINE', 'OFF', STATUS) AS STATUS,
    DECODE(CONTENTS, 'PERMANENT', 'PMNT', 'TEMPORARY', 'TEMP', CONTENTS) AS CONTENTS,
    INITIAL_EXTENT  / 1024 AS IEXT_KB,
    NEXT_EXTENT     / 1024 AS NEXT_KB,
    MIN_EXTENTS,
    MAX_EXTENTS,
    PCT_INCREASE AS PINC,
    DECODE(LOGGING, 'LOGGING', 'ON', 'NOLOGGING', 'OFF', LOGGING) AS LOG,
    SUBSTR(EXTENT_MANAGEMENT, 1, 4) AS EXT_MGR,
    DECODE(ALLOCATION_TYPE, 'UNIFORM', ALLOCATION_TYPE || '('
    || TO_CHAR(MIN_EXTLEN / 1024) || 'KB)', ALLOCATION_TYPE) AS ALCT_TYPE
FROM
(
SELECT
    D.TABLESPACE_NAME,
    NVL(U.MAXBYTES - F.BYTES, 0)     AS USED_BYTES,
    NVL(U.MAXBYTES, 0)  AS MAX_BYTES,
    NVL(F.BYTES, 0)     AS FREE_BYTES,
    (
        RPAD(
            (
                RPAD(
                    '0',
                    (
                        (
                            100 - Round( CEIL( (F.BYTES / U.MAXBYTES) * 100 ), -1 )
                        ) / 5 + 1
                    ), 'XX'
                )
            ), 21, '--'
        ) ||'['||LPAD((100-ceil((F.BYTES / U.MAXBYTES)*100)),3,0)||'%'||']') as RATE,
    D.STATUS,
    D.CONTENTS,
    NULL AS BLOCK_SIZE,
    D.INITIAL_EXTENT,
    D.NEXT_EXTENT,
    D.MIN_EXTENTS,
    D.MAX_EXTENTS,
    D.PCT_INCREASE,
    D.MIN_EXTLEN,
    D.LOGGING,
    D.EXTENT_MANAGEMENT,
    D.ALLOCATION_TYPE,
    D.PLUGGED_IN,
    NULL AS SEGMENT_SPACE_MANAGEMENT
FROM
    DBA_TABLESPACES D, 
    (
        SELECT
            TABLESPACE_NAME,
            SUM(USER_BYTES)   AS MAXBYTES
        FROM
            DBA_DATA_FILES
        GROUP BY
            TABLESPACE_NAME
    ) U,
    (
        SELECT
            TABLESPACE_NAME,
            SUM(BYTES)      AS BYTES 
        FROM
            DBA_FREE_SPACE
        GROUP BY
            TABLESPACE_NAME
    ) F 
WHERE
    (
        D.EXTENT_MANAGEMENT <> 'LOCAL'
        OR D.CONTENTS <> 'TEMPORARY'
    )
    AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+) 
    AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL
SELECT
    D.TABLESPACE_NAME,
    NVL(U.MAXBYTES, 0) - NVL(F.BYTES, 0)    AS USED_BYTES,
    NVL(U.MAXBYTES, 0)  AS MAX_BYTES,
    NVL(F.BYTES, 0) AS FREE_BYTES,
    NULL,
    D.STATUS,
    D.CONTENTS,
    NULL AS BLOCK_SIZE,
    D.INITIAL_EXTENT,
    D.NEXT_EXTENT,
    D.MIN_EXTENTS,
    D.MAX_EXTENTS,
    D.PCT_INCREASE,
    D.MIN_EXTLEN,
    D.LOGGING,
    D.EXTENT_MANAGEMENT,
    D.ALLOCATION_TYPE,
    D.PLUGGED_IN,
    NULL AS SEGMENT_SPACE_MANAGEMENT
FROM
    DBA_TABLESPACES D,
    (
        SELECT
            TABLESPACE_NAME,
            SUM(USER_BYTES)   AS MAXBYTES
        FROM
            DBA_TEMP_FILES
        GROUP BY
            TABLESPACE_NAME
    ) U,
    (
        SELECT
            TABLESPACE_NAME,
            SUM(BYTES_CACHED) AS BYTES
        FROM
            V$TEMP_EXTENT_POOL
        GROUP BY
            TABLESPACE_NAME
    ) F
WHERE
    D.EXTENT_MANAGEMENT = 'LOCAL'
    AND D.CONTENTS = 'TEMPORARY'
    AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
    AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
)
/
CLEAR COL