ADVERTISEMENT

Check Database Status

Use the following SQL queries to get key information about the Oracle database and instance status.

1. Detailed Database & Instance Info

SET PAGESIZE 50
SET LINESIZE 200 
SET VERIFY OFF
BREAK ON REPORT SKIP 1
COLUMN section_heading FORMAT A35
COLUMN section_data FORMAT A35
-- Database Information Section
SELECT 'DATABASE INFORMATION' AS section_heading, NULL AS section_data FROM dual
UNION ALL
SELECT NULL, NULL FROM dual  -- Blank line after heading
UNION ALL
SELECT 'Database Name:', db.name FROM v$database db
UNION ALL
SELECT 'Unique Name:', db.db_unique_name FROM v$database db
UNION ALL
SELECT 'Creation Date:', TO_CHAR(db.created, 'DD-MON-YYYY HH24:MI:SS') FROM v$database db
UNION ALL
SELECT 'Open Mode:', db.open_mode FROM v$database db
UNION ALL
SELECT 'Log Mode:', db.log_mode FROM v$database db
UNION ALL
SELECT 'Database Role:', db.database_role FROM v$database db
UNION ALL
SELECT 'Character Set:', 
       (SELECT value FROM v$parameter WHERE name = 'nls_character_set') FROM dual
UNION ALL
SELECT 'NCHAR Character Set:', 
       (SELECT value FROM v$parameter WHERE name = 'nls_nchar_character_set') FROM dual
UNION ALL
SELECT NULL, NULL FROM dual  -- Blank line after section
-- Instance Information Section
UNION ALL
SELECT 'INSTANCE INFORMATION' AS section_heading, NULL AS section_data FROM dual
UNION ALL
SELECT NULL, NULL FROM dual  -- Blank line after heading
UNION ALL
SELECT 'Instance Name:', inst.instance_name FROM v$instance inst
UNION ALL
SELECT 'Host Name:', inst.host_name FROM v$instance inst
UNION ALL
SELECT 'Startup Time:', TO_CHAR(inst.startup_time, 'DD-MON-YYYY HH24:MI:SS') FROM v$instance inst
UNION ALL
SELECT 'Instance Status:', inst.status FROM v$instance inst
UNION ALL
SELECT 'Database Status:', inst.database_status FROM v$instance inst
UNION ALL
SELECT 'Logins Allowed:', inst.logins FROM v$instance inst
UNION ALL
SELECT 'Version:', inst.version FROM v$instance inst
UNION ALL
SELECT NULL, NULL FROM dual  -- Blank line after section
;

2. Compact Summary

set lines 200 pages 200
col name for a15
col db_unique_name for a15
col open_mode for a15
col log_mode for a15
col logins for a15
col instance_name for a15
col HOST_NAME for a15
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
select name,db_unique_name,created,open_mode,log_mode,logins,instance_name,database_role,host_name,startup_time from v$instance,v$database;

📌 Notes:

  • Use these queries to quickly assess DB health & configuration.
  • Modify date/time formats as needed with ALTER SESSION.
  • Can be run via SQL*Plus, SQLcl, or any SQL interface.

ADVERTISEMENT