
HOW TO FETCH THE CURRENT ORACLE DATABASE STATUS
Queries
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
-------------------- | -------------------------
Written At
05 NOV 202123:20
OEL Release
7.9 x64
Database Release
19.12.0.0.0
Tags In
Share
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Session Tracing
- Sessions
- SQL Trace
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum