プロジェクト

全般

プロフィール

SQLサンプル

表領域の情報取得

SET LINESIZE 300 PAGESIZE 1024 TRIMSPOOL ON VER OFF TAB OFF

COL NAME                        FOR A32
COL VALUE                       FOR A48
COL DISPLAY_VALUE               FOR A48
COL ISDEFAULT                   FOR A10

SELECT
     NAME
    ,VALUE
    ,DISPLAY_VALUE
    ,ISDEFAULT
    ,ISADJUSTED
FROM
     V$PARAMETER
WHERE
        ISDEFAULT        = 'FALSE'
ORDER BY
     NAME
/

COL TABLESPACE_NAME             FOR A12             HEADING TABLESPACE|NAME
COL FILE_NAME                   FOR A56
COL STATUS                      FOR A10
COL MBYTES                      FOR 9G999G990
COL INCREMENT_BY                FOR 9G999G990       HEADING INCREMENT|BY(MIB)
COL AUTOEXTENSIBLE              FOR A6              HEADING AUTO|EXTEN|SIBLE
COL ONLINE_STATUS               FOR A6
COL FILE_NAME                   FOR A56
COL "TOTAL(MIB)"                FOR 999G999G990
COL "USED (MIB)"                FOR 999G999G990
COL "FREE (MIB)"                FOR 999G999G990
COL "DATAFILE USED(%)"          FOR 990D99          HEADING "DATAFILE|USED (%)"
COL "TABLESPACE USED(%)"        FOR 990D99          HEADING "TBLSPACE|USED (%)"
COL "USED(%)"                   FOR 990D99          HEADING "USED (%)"
COL ONLINE_STATUS                                   HEADING ONLINE|STATUS
COL INITIAL_EXTENT_KB           FOR 999G990         HEADING "INITIAL|EXT(KIB)"
COL ALLOCATION_TYPE             FOR A8              HEADING "ALLOC|TYPE"
COL EXTENT_MANAGEMENT           FOR A8              HEADING "EXTENT|MGMT"
COL SEGMENT_SPACE_MANAGEMENT    FOR A8              HEADING "SEG|SPACE|MGMT"
COL CONTENTS                    FOR A9

BREAK ON TABLESPACE_NAME

SELECT
     A.TABLESPACE_NAME
    ,'  (表領域計)'                                                 FILE_NAME
    ,NULL                                                           STATUS
    ,SUM(BYTES)/1024/1024                                          "TOTAL(MIB)"
--  ,SUM(BYTES-NVL(C3,0))/1024/1024                                "USED (MIB)"
    ,SUM(NVL(C3,0))/1024/1024                                      "FREE (MIB)"
    ,ROUND((SUM(BYTES-NVL(C3,0))/1024) / (SUM(BYTES)/1024)*100,2)  "USED(%)"
--  ,ROUND((SUM(BYTES-NVL(C3,0))/1024) / (SUM(BYTES)/1024)*100,2)  "DATAFILE USED(%)"
--  ,TO_NUMBER(NULL)                                               "TABLESPACE USED(%)"
    ,TO_NUMBER(NULL)                                                INCREMENT_BY
    ,NULL                                                           AUTOEXTENSIBLE
    ,NULL                                                           ONLINE_STATUS
    ,T.INITIAL_EXTENT / 1024                                        INITIAL_EXTENT_KB
    ,T.ALLOCATION_TYPE
    ,T.EXTENT_MANAGEMENT
    ,T.SEGMENT_SPACE_MANAGEMENT
    ,T.CONTENTS
FROM
     DBA_DATA_FILES     A
    ,DBA_TABLESPACES    T
    ,(
        SELECT
             TABLESPACE_NAME    C1
            ,FILE_ID            C2
            ,SUM(BYTES)         C3
        FROM
             DBA_FREE_SPACE
        GROUP BY
             TABLESPACE_NAME
            ,FILE_ID
     )  B
WHERE
        A.TABLESPACE_NAME    = B.C1 ( + )
    AND A.FILE_ID            = B.C2 ( + )
    AND A.TABLESPACE_NAME    = T.TABLESPACE_NAME
GROUP BY
     A.TABLESPACE_NAME
    ,T.INITIAL_EXTENT
    ,T.ALLOCATION_TYPE
    ,T.EXTENT_MANAGEMENT
    ,T.SEGMENT_SPACE_MANAGEMENT
    ,T.CONTENTS
UNION ALL
SELECT
     TABLESPACE_NAME
    ,FILE_NAME
    ,STATUS
    ,BYTES/1024/1024                                                "TOTAL(MIB)"
--  ,(BYTES-NVL(C3,0))/1024/1024                                    "USED (MIB)"
    ,NVL(C3,0)/1024/1024                                            "FREE (MIB)"
--  ,TO_NUMBER(NULL)                                                "DATAFILE USED(%)"
--  ,ROUND(((BYTES-NVL(C3,0))/1024)/(BYTES/1024)*100,2)             "TABLESPACE USED(%)"
    ,ROUND(((BYTES-NVL(C3,0))/1024)/(BYTES/1024)*100,2)             "USED(%)"
    ,INCREMENT_BY
    ,AUTOEXTENSIBLE
    ,ONLINE_STATUS
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM
     DBA_DATA_FILES A
    ,(
        SELECT
             TABLESPACE_NAME    C1
            ,FILE_ID            C2
            ,SUM(BYTES)         C3
        FROM
             DBA_FREE_SPACE
        GROUP BY
             TABLESPACE_NAME, FILE_ID
     )  B
WHERE
        A.TABLESPACE_NAME    = B.C1 ( + )
    AND A.FILE_ID            = B.C2 ( + )
UNION ALL
SELECT
     DT.TABLESPACE_NAME
    ,'  (表領域計)'                                                 FILE_NAME
    ,NULL                                                           STATUS
    ,SUM(DT.BYTES)/1024/1024                                       "TOTAL(MIB)"
--  ,SUM(T.BYTES_CACHED)/1024/1024                                 "USED (MIB)"
    ,SUM(DT.BYTES - T.BYTES_CACHED)/1024/1024                      "FREE (MIB)"
    ,ROUND(SUM(T.BYTES_CACHED)/SUM(DT.BYTES)*100,2)                "USED(%)"
--  ,ROUND(SUM(T.BYTES_CACHED)/SUM(DT.BYTES)*100,2)                "DATAFILE USED(%)"
--  ,TO_NUMBER(NULL)                                               "TABLESPACE USED(%)"
    ,TO_NUMBER(NULL)                                                INCREMENT_BY
    ,NULL                                                           AUTOEXTENSIBLE
    ,NULL                                                           ONLINE_STATUS
    ,X.INITIAL_EXTENT / 1024                                        INITIAL_EXTENT_KB
    ,X.ALLOCATION_TYPE
    ,X.EXTENT_MANAGEMENT
    ,X.SEGMENT_SPACE_MANAGEMENT
    ,X.CONTENTS
FROM
     DBA_TEMP_FILES         DT
    ,DBA_TABLESPACES        X
    ,V$TEMP_EXTENT_POOL     T
    ,V$TEMPFILE             V
WHERE
        T.FILE_ID ( + )      = DT.FILE_ID
    AND DT.FILE_ID           = V.FILE#
    AND DT.TABLESPACE_NAME   = X.TABLESPACE_NAME
GROUP BY
     DT.TABLESPACE_NAME
    ,X.INITIAL_EXTENT
    ,X.ALLOCATION_TYPE
    ,X.EXTENT_MANAGEMENT
    ,X.SEGMENT_SPACE_MANAGEMENT
    ,X.CONTENTS
UNION ALL
SELECT
     DT.TABLESPACE_NAME
    ,DT.FILE_NAME
    ,DT.STATUS
    ,DT.BYTES / 1024 / 1024                                        "TOTAL(MIB)"
--  ,T.BYTES_CACHED / 1024 / 1024                                  "USED (MIB)" 
    ,(DT.BYTES - T.BYTES_CACHED) / 1024 / 1024                     "FREE (MIB)" 
--  ,TO_NUMBER(NULL)                                               "DATAFILE USED(%)"
--  ,ROUND(T.BYTES_CACHED / DT.BYTES * 100, 2)                     "TABLESPACE USED(%)"
    ,ROUND(T.BYTES_CACHED / DT.BYTES * 100, 2)                     "USED(%)"
    ,INCREMENT_BY
    ,AUTOEXTENSIBLE
    ,NULL                                                           ONLINE_STATUS
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM
     DBA_TEMP_FILES         DT
    ,V$TEMP_EXTENT_POOL     T
    ,V$TEMPFILE             V
WHERE
        T.FILE_ID ( + )      = DT.FILE_ID
    AND DT.FILE_ID           = V.FILE#
ORDER BY
     TABLESPACE_NAME
    ,FILE_NAME
/