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;
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
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;
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%';
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');
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;
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.
