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