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');