Common Oracle DBA Monitoring Commands for Beginners (Single Instance)

 

This blog post provides a foundational set of Oracle DBA commands, primarily SQL queries, that are essential for monitoring a single instance Oracle database. These commands will help beginners understand the basic health, activity, and performance of their database.

Note: Most of these commands are executed within SQL*Plus or SQL Developer, connected as a user with appropriate privileges (e.g., SYSDBA or SYSTEM).

1. Instance Status and Basic Information

These commands help you quickly ascertain the database's operational status and gather fundamental information.

1.1 Check Database Instance Status

This is the first command to check if your database instance is up and running.

SELECT STATUS FROM V$INSTANCE;

• Explanation: V$INSTANCE is a dynamic performance view that displays current instance status information. STATUS indicates whether the instance is OPEN, MOUNTED, or STARTED.

• Expected Output: OPEN (most common for a running database), MOUNTED, STARTED, SHUTDOWN.

1.2 Get Database Name and Instance Name

Useful for verifying which database and instance you are connected to.

SELECT NAME, INSTANCE_NAME FROM V$DATABASE, V$INSTANCE;

• Explanation: V$DATABASE provides database-wide information, while V$INSTANCE gives instance-specific details.

• Expected Output: Your database name and instance name.

1.3 Check Database Open Mode

Confirms if the database is open for read/write operations.

SELECT OPEN_MODE FROM V$DATABASE;

• Explanation: OPEN_MODE indicates the database's current open status (e.g., READ WRITE, READ ONLY).

• Expected Output: READ WRITE (typical), READ ONLY.

1.4 Show Current Database Version

Important for knowing your database's capabilities and for troubleshooting.

SELECT * FROM V$VERSION;

• Explanation: V$VERSION displays the version banners of various Oracle components.

• Expected Output: Oracle Database version (e.g., Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production).

2. Session Monitoring

Understanding who is connected and what they are doing is crucial for performance and security.

2.1 List All Active Sessions

Identifies currently connected users and their activities.

SELECT SID, SERIAL#, USERNAME, PROGRAM, STATUS, MACHINE, OSUSER
FROM V$SESSION
WHERE USERNAME IS NOT NULL
ORDER BY USERNAME, SID;

• Explanation: V$SESSION provides information about active sessions.

  • SID: Session ID.

  • SERIAL#: Session serial number (used with SID to uniquely identify a session).

  • USERNAME: Database user connected.

  • PROGRAM: Application or program connecting to the database.

  • STATUS: Session status (e.g., ACTIVE, INACTIVE, SNIPED).

  • MACHINE: Client machine name.

  • OSUSER: Operating system user.

• Expected Output: A list of all active and inactive user sessions.

2.2 Find Sessions Waiting for Resources

Identifies sessions that are currently experiencing waits, which can indicate performance bottlenecks.

SELECT SID, SERIAL#, USERNAME, EVENT, WAIT_CLASS, SECONDS_IN_WAIT, STATE
FROM V$SESSION
WHERE STATUS = 'ACTIVE' AND WAIT_CLASS != 'Idle'
ORDER BY SECONDS_IN_WAIT DESC;

• Explanation: Filters V$SESSION for active sessions that are not idle and orders them by the duration of their current wait.

  • EVENT: The specific wait event the session is waiting on.

  • WAIT_CLASS: Category of the wait event (e.g., User I/O, Concurrency, Application).

  • SECONDS_IN_WAIT: How long the session has been waiting for the current event.

• Expected Output: Sessions currently waiting, along with the wait event details.

2.3 Find SQL Being Executed by a Session

Helps pinpoint what a specific session is currently running.

SELECT S.SID, S.SERIAL#, S.USERNAME, S.STATUS, S.MACHINE, S.PROGRAM,
       SQL.SQL_FULLTEXT
FROM V$SESSION S, V$SQL SQL
WHERE S.SQL_ID = SQL.SQL_ID
AND S.STATUS = 'ACTIVE'
AND S.USERNAME IS NOT NULL
ORDER BY S.LAST_CALL_ET DESC; -- Order by elapsed time since last call for recent activity

• Explanation: Joins V$SESSION with V$SQL (which contains SQL text) using SQL_ID.

• Expected Output: Active sessions and the full text of the SQL statement they are executing.

3. Storage Monitoring

Monitoring storage ensures you don't run out of space and helps manage database growth.

3.1 Check Tablespace Usage

Crucial for monitoring free space within your database.

SELECT
    DF.TABLESPACE_NAME,
    TOTAL_SPACE_MB,
    FREE_SPACE_MB,
    (TOTAL_SPACE_MB - FREE_SPACE_MB) AS USED_SPACE_MB,
    ROUND((TOTAL_SPACE_MB - FREE_SPACE_MB) / TOTAL_SPACE_MB * 100, 2) AS USED_PERCENTAGE
FROM
    (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS TOTAL_SPACE_MB
     FROM DBA_DATA_FILES
     GROUP BY TABLESPACE_NAME) DF,
    (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS FREE_SPACE_MB
     FROM DBA_FREE_SPACE
     GROUP BY TABLESPACE_NAME) FS
WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME (+)
ORDER BY USED_PERCENTAGE DESC;

• Explanation: Joins DBA_DATA_FILES (total space) with DBA_FREE_SPACE (free space) to calculate usage. DBA_DATA_FILES and DBA_FREE_SPACE are data dictionary views.

• Expected Output: A list of tablespaces, their total size, free space, used space, and percentage used.

3.2 List Datafiles and Their Sizes

Provides a clear view of where your database files reside and their current sizes.

SELECT FILE_NAME, TABLESPACE_NAME, BYTES / 1024 / 1024 AS SIZE_MB, AUTOEXTENSIBLE, MAXBYTES / 1024 / 1024 AS MAX_SIZE_MB
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME, FILE_NAME;

• Explanation: DBA_DATA_FILES lists all datafiles, their associated tablespaces, and autoextend settings.

• Expected Output: Datafile paths, tablespace names, current sizes, and autoextend details.

3.3 Check Temp Tablespace Usage

Monitors temporary segment usage, important for sorting and hash operations.

SELECT
    A.TABLESPACE_NAME,
    A.TOTAL_TEMP_SPACE_MB,
    ROUND(SUM(B.BYTES_USED) / 1024 / 1024, 2) AS USED_TEMP_SPACE_MB,
    ROUND(SUM(B.BYTES_FREE) / 1024 / 1024, 2) AS FREE_TEMP_SPACE_MB,
    ROUND((SUM(B.BYTES_USED) / A.TOTAL_TEMP_SPACE_MB) * 100, 2) AS USED_PERCENTAGE
FROM
    (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS TOTAL_TEMP_SPACE_MB
     FROM DBA_TEMP_FILES
     GROUP BY TABLESPACE_NAME) A,
    V$TEMP_SPACE_HEADER B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
GROUP BY A.TABLESPACE_NAME, A.TOTAL_TEMP_SPACE_MB
ORDER BY USED_PERCENTAGE DESC;

• Explanation: Combines DBA_TEMP_FILES (total temp space) with V$TEMP_SPACE_HEADER (current usage).

• Expected Output: Temp tablespace usage statistics.

4. Performance Monitoring

These commands give you insights into the database's performance metrics.

4.1 Check SGA (System Global Area) Usage

The SGA is a critical memory structure. Monitoring its usage helps understand memory allocation.

SELECT NAME, ROUND(BYTES / 1024 / 1024, 2) AS SIZE_MB
FROM V$SGASTAT
ORDER BY SIZE_MB DESC;

• Explanation: V$SGASTAT provides detailed information about the components of the SGA.

• Expected Output: Sizes of different SGA components (e.g., Buffer Cache, Shared Pool, Log Buffer).

4.2 Check PGA (Program Global Area) Usage

PGA is memory allocated to individual processes. High PGA usage can indicate inefficient SQL or large sorts/hashes.

SELECT NAME, VALUE / 1024 / 1024 AS SIZE_MB
FROM V$PGASTAT
WHERE NAME LIKE '%aggregate PGA target%' OR NAME LIKE '%total PGA allocated%';

• Explanation: V$PGASTAT shows PGA memory usage statistics.

• Expected Output: Aggregate PGA target and total PGA currently allocated.

4.3 Monitor Redo Log Switches

Frequent redo log switches can indicate high database activity and potential I/O bottlenecks.

SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS SWITCH_TIME,
       SEQUENCE# AS REDO_SEQUENCE,
       ARCHIVED AS ARCHIVED_STATUS
FROM V$ARCHIVED_LOG
ORDER BY COMPLETION_TIME DESC
FETCH FIRST 10 ROWS ONLY; -- Shows last 10 log switches

• Explanation: V$ARCHIVED_LOG provides information about archived redo logs.

• Expected Output: A list of recent redo log switches, their sequence numbers, and archive status.

4.4 Check Buffer Cache Hit Ratio

A high buffer cache hit ratio (ideally > 90%) indicates that most data blocks are found in memory, reducing physical I/O.

SELECT
    (SUM(DECODE(NAME, 'consistent gets', VALUE, 0)) +
     SUM(DECODE(NAME, 'db block gets', VALUE, 0)) -
     SUM(DECODE(NAME, 'physical reads', VALUE, 0))) * 100 /
    (SUM(DECODE(NAME, 'consistent gets', VALUE, 0)) +
     SUM(DECODE(NAME, 'db block gets', VALUE, 0))) AS BUFFER_CACHE_HIT_RATIO
FROM V$SYSSTAT
WHERE NAME IN ('consistent gets', 'db block gets', 'physical reads');

• Explanation: Calculates the hit ratio using system statistics from V$SYSSTAT.

• Expected Output: A single value representing the buffer cache hit ratio.

5. Alert Log Monitoring

The alert log is the primary diagnostic file for an Oracle database. It records important messages and errors.

5.1 Locate the Alert Log File

You need to know where the alert log is located to view its contents.

SHOW PARAMETER BACKGROUND_DUMP_DEST;

• Explanation: BACKGROUND_DUMP_DEST is an initialization parameter that specifies the directory where background process trace files (including the alert log) are written.

• Expected Output: The file path to the diagnostic destination. The alert log file itself will be named log.xml (for 11gR2 and later) or alert_<SID>.log (for older versions) within a subdirectory of this path (e.g., diag/rdbms/<dbname>/<instname>/trace/).

Note: You typically view the alert log using operating system commands (e.g., tail -f alert_<SID>.log on Linux/Unix, or opening log.xml in a text editor/browser). There isn't a direct SQL command to read its content live.

6. Other Useful Commands

6.1 Show All Initialization Parameters

Displays all current database initialization parameters and their values.

SHOW PARAMETERS;

• Explanation: A quick way to see the database configuration.

• Expected Output: A long list of all parameters. You can filter this, e.g., SHOW PARAMETER DB_BLOCK_SIZE;.

6.2 Check Current Date and Time

Useful for verifying the database server's time.

SELECT SYSDATE FROM DUAL;

• Explanation: SYSDATE returns the current date and time. DUAL is a dummy table used for queries that don't need to retrieve data from a specific taable.

• Expected Output: Current date and time.

Conclusion

This set of commands provides a solid starting point for any beginner Oracle DBA to monitor a single instance database. Regularly using these commands will help you understand your database's behavior, identify potential issues early, and gain confidence in managing Oracle environments. As you become more comfortable, you can explore more advanced V$ and DBA_ views for deeper insights.

Oracle DBA Commands Notebook: A Mini Survival Guide

 

Whether you’re spinning up a RAC lab, auditing system usage, or simply trying to stay sharp as an Oracle DBA, this notebook packs a punch with essential SQL snippets for every skill level. Copy, paste, and conquer! ⚡

🟢 Beginner Commands — The Essentials

🔍 Check Oracle Database Version

SELECT * FROM v$version;

Great for confirming your environment—especially useful before patching or feature testing.

👤 List All Users

SELECT username FROM dba_users ORDER BY username;

Quick way to verify access or hunt for unused accounts.

🗃️ Show Tablespaces

SELECT tablespace_name, status FROM dba_tablespaces;

Verify availability and health of your storage allocations.

🟡 Intermediate Commands — Digging Deeper

📁 Datafiles by Tablespace

SELECT tablespace_name, file_name, bytes/1024/1024 AS MB FROM dba_data_files ORDER BY tablespace_name;

See how your datafiles are distributed and consuming space.

📊 Tablespace Usage Overview

SELECT df.tablespace_name, df.total_mb, fs.free_mb, (df.total_mb - fs.free_mb) AS used_mb, ROUND((df.total_mb - fs.free_mb)/df.total_mb*100, 2) AS pct_used FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb FROM dba_data_files GROUP BY tablespace_name) df JOIN (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb FROM dba_free_space GROUP BY tablespace_name) fs ON df.tablespace_name = fs.tablespace_name;



Your go-to for capacity planning and alert thresholds.

🧑‍💻 List Running Sessions

SELECT sid, serial#, username, status, osuser, machine, program FROM v$session WHERE username IS NOT NULL;

Useful for monitoring active connections, especially in multi-user setups.

🔴 Advanced Commands — When You Need Firepower

⛓️ Blocking Sessions Detector

SELECT s1.sid || ',' || s1.serial# AS blocker, s2.sid || ',' || s2.serial# AS blocked, s1.username AS blocker_user, s2.username AS blocked_user FROM v$lock l1 JOIN v$session s1 ON l1.sid = s1.sid JOIN v$lock l2 ON l2.block = 1 AND l2.id1 = l1.id1 AND l2.id2 = l1.id2 JOIN v$session s2 ON l2.sid = s2.sid;

Debug locks and performance bottlenecks in real time.

🔥 Top SQL by CPU Usage

SELECT * FROM ( SELECT sql_id, parsing_schema_name, cpu_time/1000000 AS cpu_seconds, executions, sql_text FROM v$sql ORDER BY cpu_time DESC ) WHERE ROWNUM <= 10;

Spot expensive queries before they throttle the system.

📦 Archive Log Status

ARCHIVE LOG LIST;

A fast way to verify if your DB is in archive log mode—critical for RMAN and PITR operations.

⏱️ Wait Event Breakdown

SELECT event, total_waits, time_waited/100 AS seconds_waited FROM v$system_event ORDER BY time_waited DESC FETCH FIRST 10 ROWS ONLY;

Helps diagnose performance issues by identifying system-level wait events.