'DB 상태 조회'에 해당되는 글 1건

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;