Oracle DB 기본 상태 조회 스크립트

Oracle DB 기본 상태 조회 스크립트

---

SELECT 'MEASURE TIME' FTYPE,        TO_CHAR (SYSDATE, 'YYYY.MM.DD HH24:MI:SS') NAME,        NULL VALUE,        NULL NOTE   FROM DUAL UNION ALL SELECT 'CURRENT SCN' FTYPE,        TO_CHAR (CURRENT_SCN, 'FM999,999,999,999,999,999,999') NAME,        NULL,        NULL   FROM GV$DATABASE  WHERE INST_ID = (select INST_ID from GV$DATABASE)   UNION ALL SELECT 'DB_NAME' FTYPE,        DB_NAME,        NULL VALUE,        NULL VTYPE   FROM DBA_HIST_DATABASE_INSTANCE  WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)           AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')  UNION ALL SELECT 'DBID' FTYPE,        TO_CHAR (DBID) NAME,        NULL,        NULL   FROM DBA_HIST_DATABASE_INSTANCE  WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)          AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')  UNION ALL SELECT 'INSTANCE NUMBER' FTYPE,        TO_CHAR (INSTANCE_NUMBER) NAME,        NULL,        NULL   FROM DBA_HIST_DATABASE_INSTANCE  WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)          AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS') UNION ALL SELECT 'INSTANCE NAME' FTYPE,        INSTANCE_NAME NAME,        NULL,        NULL   FROM DBA_HIST_DATABASE_INSTANCE  WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)          AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS') UNION ALL SELECT 'HOST NAME' FTYPE,        HOST_NAME NAME,        NULL,        NULL   FROM DBA_HIST_DATABASE_INSTANCE  WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)          AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS') UNION ALL SELECT 'STARTUP TIME' FTYPE,        TO_CHAR (STARTUP_TIME, 'YYYY.MM.DD HH24:MI:SS') NAME,        NULL,        NULL   FROM DBA_HIST_DATABASE_INSTANCE  WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)          AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS') UNION ALL SELECT 'VERSION' FTYPE,        VERSION NAME,        NULL,        NULL   FROM DBA_HIST_DATABASE_INSTANCE  WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)          AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS') UNION ALL SELECT 'PARAM' FTYPE,        PARAMETER_NAME NAME,        TO_CHAR (VALUE) VALUE,        NULL   FROM DBA_HIST_PARAMETER  WHERE DBID = (select dbid from v$database)    AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)      AND SNAP_ID = 873  UNION ALL SELECT 'SGA' FTYPE,        NAME,        ROUND (VALUE / 1024 / 1024, 1) || ' MB' VALUE,        NULL   FROM DBA_HIST_SGA  WHERE DBID = (select dbid from v$database)    AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)      AND SNAP_ID = 873  UNION ALL SELECT 'PGA' FTYPE,        NAME,        ROUND (VALUE / 1024 / 1024, 1) || ' MB' VALUE,        NULL   FROM DBA_HIST_PGASTAT  WHERE DBID = (select dbid from v$database)   AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)     AND SNAP_ID = 873  UNION ALL SELECT 'CURRENT SGA' FTYPE,        NAME,        ROUND (BYTES / 1024 / 1024, 1) || ' MB' VALUE,        RESIZEABLE VTYPE   FROM GV$SGAINFO  WHERE INST_ID = (select distinct INST_ID from  GV$SGAINFO)   UNION ALL SELECT 'CURRENT PGA' FTYPE,        NAME,        DECODE (           UNIT,           'bytes', TO_CHAR (ROUND (VALUE / 1024 / 1024, 1),                             'FM999,999,999,999.9'),           TO_CHAR (VALUE))           VALUE,        DECODE (UNIT, 'bytes', 'MB', UNIT) VTYPE   FROM GV$PGASTAT  WHERE INST_ID = (select distinct INST_ID from   GV$PGASTAT)   UNION ALL SELECT 'CURRENT SIZE : SEGMENT' FTYPE,        TO_CHAR (SUM (BLOCKS), 'FM999,999,999,999') || ' Blocks',        TO_CHAR (ROUND ( (SUM (BYTES) / 1024 / 1024 / 1024), 1),                 'FM999,999,999,999')        || ' GB',        NULL VTYPE   FROM DBA_SEGMENTS UNION ALL SELECT 'CURRENT SIZE : DATAFILE' FTYPE,        TO_CHAR (SUM (BLOCKS), 'FM999,999,999,999') || ' Blocks',        TO_CHAR (ROUND ( (SUM (BYTES) / 1024 / 1024 / 1024), 1),                 'FM999,999,999,999')        || ' GB',        NULL VTYPE   FROM DBA_DATA_FILES ORDER BY 1, 2;