Query To Check Database Status and Properties

Below query will give the Oracle Database Version, startup time, whether database is RAC enabled, archivelog status, default temporary and permanent tablespace of an database etc.


Download(dbstatus.sql)

REM +======================================================================+
REM                    
REM File Name: dbstatus.sql
REM 
REM Description:
REM   Query To Check Database Status and Properties
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @dbstatus.sql 
REM   
REM +======================================================================+

clear columns

PROMPT
PROMPT ...Database Status...
PROMPT

set echo off heading on underline on;
set lines 180
column inst_num  format 99999;
column inst_name format a12;
column db_name   format a12;
column dbid      format 9999999999 just c;
column OPEN_MODE format a12 WORD_WRAPPED
column VERSION format a12 WORD_WRAPPED

select i.instance_number inst_num
  , d.dbid            dbid
  , i.instance_name   inst_name
  , d.name            db_name
  , i.VERSION
  , d.OPEN_MODE       
  , TO_CHAR(d.CREATED, 'DD-MON-YYYY HH24:MI:SS') "CREATED"
  , TO_CHAR(i.STARTUP_TIME, 'DD-MON-YYYY HH24:MI:SS') "STARTUP_TIME"
  , d.LOG_MODE
  from gv$database d,
    gv$instance i;

PROMPT
PROMPT ...Database Properties...
PROMPT

col PROPERTY_VALUE for a50
SELECT property_name, property_value
  FROM database_properties
 WHERE property_name IN ('DEFAULT_TBS_TYPE', 'DEFAULT_PERMANENT_TABLESPACE', 'DEFAULT_TEMP_TABLESPACE', 'NLS_LANGUAGE', 'NLS_CHARACTERSET');