RBS表示 - ぐま@Wiki

【Oracle】-SQL-RBS表示

set line 1000
COL OWNER           FORMAT A15
COL SEGMENT_NAME    FORMAT A15
COL TABLESPACE_NAME FORMAT A15
COL IEXT_KB         FORMAT 9,999,990
COL NEXT_KB         FORMAT 9,999,990
COL MAX_EXTENTS     FORMAT 9,999,999,990
COL PINC            FORMAT 990
COL USED_MB         FORMAT 999,990
COL MAX_MB          FORMAT 999,990
COL FREE_MB         FORMAT 999,990
COL STATUS          FORMAT A8
COL EXT_MGR         FORMAT A4
COL ALCT_TYPE       FORMAT A20
SELECT
    DRS.OWNER,
    DRS.SEGMENT_NAME,
    DRS.TABLESPACE_NAME,
    DRS.STATUS,
    DRS.INITIAL_EXTENT  / 1024 AS IEXT_KB,
    DRS.NEXT_EXTENT     / 1024 AS NEXT_KB,
    DRS.MAX_EXTENTS,
    DRS.PCT_INCREASE AS PINC,
    (DDF.MAX_BYTES - DFS.FREE_BYTES)  / 1024 / 1024 AS USED_MB,
    DDF.MAX_BYTES   / 1024 / 1024 AS MAX_MB,
    DFS.FREE_BYTES  / 1024 / 1024 AS FREE_MB,
    DECODE(DT.LOGGING, 'LOGGING', 'ON', 'NOLOGGING', 'OFF', DT.LOGGING) AS LOG,
    SUBSTR(DT.EXTENT_MANAGEMENT, 1, 4) AS EXT_MGR,
    DECODE(DT.ALLOCATION_TYPE, 'UNIFORM', DT.ALLOCATION_TYPE || '(' || TO_CHAR(DT.MIN_EXTLEN / 1024) || 'KB)', DT.ALLOCATION_TYPE) AS ALCT_TYPE
FROM
    DBA_ROLLBACK_SEGS DRS,
    DBA_TABLESPACES DT,
    (
        SELECT
            TABLESPACE_NAME,
            SUM(USER_BYTES)   AS MAX_BYTES
        FROM
            DBA_DATA_FILES
        GROUP BY
            TABLESPACE_NAME
    ) DDF,
    (
        SELECT
            TABLESPACE_NAME,
            SUM(BYTES)      AS FREE_BYTES
        FROM
            DBA_FREE_SPACE
        GROUP BY
            TABLESPACE_NAME
    ) DFS
WHERE
    DRS.TABLESPACE_NAME = DDF.TABLESPACE_NAME
    AND DRS.TABLESPACE_NAME = DFS.TABLESPACE_NAME
    AND DRS.TABLESPACE_NAME = DT.TABLESPACE_NAME
/
CLEAR COL