HOW TO FETCH THE CURRENT ORACLE DATABASE STATUS

A query to fetch a current status of an Oracle Database.

DEMO

DEMO : HOW TO TRANSPOSE DATA FROM COLUMNS TO ROWS IN AN ORACLE DATABASE

QUERY

The DBT_DBSTATUS view displays the current status of an Oracle Database.

------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 15:44 13-Nov-2021 (002)
--
-- DBT_DBSTATUS - DATABASE TOOLS, DATABASE STATUS
--
-- The DBT_DBSTATUS view displays the current database status.
--
------------------------------------------------------------
-- CREATE OR REPLACE VIEW DBT_DBSTATUS AS 
WITH DATABASE_STATUS AS (
SELECT CAST(VD.DBID AS VARCHAR2(16)) DBID,
       VD.NAME DB_NAME,
       VD.DB_UNIQUE_NAME,
       VI.INSTANCE_NAME,
       CAST(VI.INSTANCE_NUMBER AS VARCHAR2(10)) INSTANCE_NUMBER,
       VI.VERSION,
       VI.HOST_NAME,
       UTL_INADDR.GET_HOST_ADDRESS() IP,
       VD.DATABASE_ROLE,
       VI.STATUS || ' ' || VD.OPEN_MODE STATUS,
       VI.LOGINS,
       VD.PROTECTION_MODE,
       VI.DATABASE_STATUS,
       VD.LOG_MODE,
       VI.ARCHIVER,
       CAST(VI.THREAD# AS VARCHAR2(10)) THREAD#,
       VD.FLASHBACK_ON,
       VD.FORCE_LOGGING,
       VD.DATAGUARD_BROKER,
       TO_CHAR(VD.CREATED,'DD-MON-YYYY HH24:MI:SS') CREATED,
       TO_CHAR(VI.STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') STARTUP_TIME
FROM
       V$DATABASE VD CROSS JOIN V$INSTANCE VI
)
SELECT * FROM DATABASE_STATUS
UNPIVOT 
(
       VALUE FOR PROPERTY IN (
       DBID,DB_NAME,DB_UNIQUE_NAME,INSTANCE_NAME,INSTANCE_NUMBER,VERSION,
       HOST_NAME,IP,DATABASE_ROLE,STATUS,LOGINS,PROTECTION_MODE,DATABASE_STATUS,
       LOG_MODE,ARCHIVER,THREAD#,FLASHBACK_ON,FORCE_LOGGING,DATAGUARD_BROKER,
       CREATED,STARTUP_TIME
))
UNION ALL
SELECT RPAD('-',20,'-'), RPAD('-',25,'-') FROM DUAL;

EXAMPLE

SET FEEDBACK OFF
SET SQLNUMBER OFF
SET COLSEP " | "

SET LINES 300
SET PAGES 999
COL VALUE FOR A25
COL PROPERTY FOR A20

SELECT * FROM DBT_DBSTATUS;

PROPERTY             | VALUE
-------------------- | -------------------------
DBID                 | 1164677709
DB_NAME              | TST19C
DB_UNIQUE_NAME       | tst19c
INSTANCE_NAME        | tst19c
INSTANCE_NUMBER      | 1
VERSION              | 19.0.0.0.0
HOST_NAME            | 19c.vmware.local
IP                   | 192.168.91.100
DATABASE_ROLE        | PRIMARY
STATUS               | OPEN READ WRITE
LOGINS               | ALLOWED
PROTECTION_MODE      | MAXIMUM PERFORMANCE
DATABASE_STATUS      | ACTIVE
LOG_MODE             | NOARCHIVELOG
ARCHIVER             | STOPPED
THREAD#              | 1
FLASHBACK_ON         | NO
FORCE_LOGGING        | NO
DATAGUARD_BROKER     | DISABLED
CREATED              | 16-OCT-2021 17:36:13
STARTUP_TIME         | 13-NOV-2021 15:39:55
-------------------- | -------------------------