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.