「表領域表示」の編集履歴（バックアップ）一覧はこちら

「表領域表示」(2006/10/18 (水) 22:30:58) の最新版変更点

追加された行は緑色になります。

削除された行は赤色になります。

*【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

*【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