db_info.sql

REM +++++++++++++++++++++++++++
REM Module: db_info.sql
REM Author: DR Timothy S Hall http://www.oracle-base.com/dba/monitoring/health.sql
REM Description: list all the tables and indexes nearing max_extents
REM              used by script health_db.sql
REM Usage: db_info.sql
REM +++++++++++++++++++++++++++
COL NAME FORM A10
SET LINES 132
SELECT DBID, NAME,OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS, DATAGUARD_BROKER, FORCE_LOGGING FROM V$DATABASE;

SET LINES 150
COL STATUS FORM A10
COL VERSION FORM A15
COL HOST_NAME FORM A35
SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, STATUS, ARCHIVER, DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE FROM V$INSTANCE;

SELECT * FROM V$VERSION;

COL NAME FORM A20
SELECT a.name,
       a.value
FROM   v$sga a;


SET LINES 132
COL STATUS FORM A10
COL NAME FORM A90
SELECT Substr(c.name,1,60) "Controlfile",
       NVL(c.status,'UNKNOWN') "Status"
FROM   v$controlfile c
ORDER BY 1;

SET PAGES 500
COL NAME FORM A90
SELECT d.name,
       NVL(d.status,'UNKNOWN') "Status",
       d.enabled "Enabled",
       nvl(d.bytes/1024/1024,0) size_in_mb
FROM   v$datafile d
ORDER BY 1;

COL MEMBER FORM A90
SELECT l.group# "Group",
       l.member,
       NVL(l.status,'UNKNOWN') "Status"
FROM   v$logfile l
ORDER BY 1,2;