Skip to content

Database Info (db.sql)

What This Script Does

Displays basic database name, instance name, host name, and instance status in a single query.

The Script

rem  db.sql
rem
ttitle 'Database and Instance Information'
rem
set lines 160
rem
clear col
col DATABASE format a12 heading 'DATABASE'
col INSTANCE format a12 heading 'INSTANCE'
col HOST format a22 heading 'HOST'
col STATUS format a12 heading 'STATUS'
rem

SELECT a.NAME DATABASE, upper(b.INSTANCE_NAME) INSTANCE, upper(b.HOST_NAME) HOST, b.STATUS STATUS
FROM v$database a, v$instance b
/

Usage

-- Basic usage
@db.sql

Parameters

The script prompts for:

None - this script requires no parameters

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON V$DATABASE
SELECT ON V$INSTANCE

Sample Output

DATABASE    INSTANCE     HOST                 STATUS
----------- ------------ -------------------- ------------
ORCL        ORCL1        DB-SERVER-01         OPEN

Key Output Columns

DATABASE: Database name from V$DATABASE
INSTANCE: Instance name (uppercase)
HOST: Host/server name (uppercase)
STATUS: Current instance status (OPEN, MOUNTED, etc.)

Common Use Cases

Quick Environment Check

-- Verify which database and instance you're connected to
@db.sql

RAC Environment

-- Identify specific instance in a cluster
@db.sql

RAC Instance Info - Detailed RAC instance information
Table Analysis - Table storage analysis