Retrieve All Key Oracle Database Information with a Single Query

Managing an Oracle database requires easy access to critical information for performance tuning, troubleshooting, and monitoring. In this post, we’ll show you how to retrieve detailed database and instance information with a simple SQL query. This query provides essential details like the database name, creation date, and status in an organized format.

The query pulls data from Oracle’s dynamic performance views (v$database and v$instance) to present the information clearly.

SQL Query to Extract Key Database and Instance Information:

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
;

Explanation of the Query:

This query retrieves detailed database and instance information in two distinct sections:

1. Database Information Section: This section pulls vital data about the Oracle database, such as its name, unique name, creation date, open mode, log mode, and character set.
2. Instance Information Section: This section provides details about the instance running the Oracle database, including the instance name, host name, startup time, instance status, database status, logins allowed, and version.

Each section is clearly labeled with a heading (e.g., “DATABASE INFORMATION” and “INSTANCE INFORMATION”) followed by the relevant details in the subsequent rows.

Example Output:

Running the above query will produce the following output format:

SECTION_HEADING                     SECTION_DATA
----------------------------------- -----------------------------------
DATABASE INFORMATION

Database Name:                      ORCL
Unique Name:                        orcl
Creation Date:                      29-AUG-2023 01:42:36
Open Mode:                          READ WRITE
Log Mode:                           NOARCHIVELOG
Database Role:                      PRIMARY
Character Set:
NCHAR Character Set:

INSTANCE INFORMATION

Instance Name:                      orcl
Host Name:                          LAPTOP-8NSE56T7
Startup Time:                       15-DEC-2024 18:16:17
Instance Status:                    OPEN
Database Status:                    ACTIVE
Logins Allowed:                     ALLOWED
Version:                            19.0.0.0.0

21 rows selected.

Or you can use below query:

set lines 200 pages 200
col name for a15
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;

 Tablespace   |DB_UNIQUE_NAME   |CREATED            |OPEN_MODE      |LOG_MODE       |LOGINS    |INSTANCE_NAME  |DATABASE_ROLE         |HOST_NAME      |STARTUP_TIME
---------------|----------------|-------------------|---------------|---------------|--------- |---------------|----------------------|---------------|-------------------
ORCL           |orcl            |29/08/2023 01:42:36|READ WRITE     |NOARCHIVELOG   |ALLOWED   |orcl           |PRIMARY               |LAPTOP-8NSE83T7|15/12/2024 18:16:17

Conclusion:

In Oracle, being able to quickly gather essential database and instance information is crucial for routine maintenance, troubleshooting, and monitoring. This SQL query provides a clean and effective way to display such information in a structured format, saving you time when you need to assess your Oracle environment. By using v$database and v$instance, this query can help ensure you have all the important details right at your fingertips.