Oracle SQL Patterns Cheat Sheet — Common Query Patterns for DBAs

Oracle SQL Patterns Cheat Sheet

Common Oracle SQL patterns and query templates that every DBA needs. Copy, adapt, and use these patterns in your daily work.

---

Top-N Queries

Top N rows (12c+ FETCH FIRST)

SELECT employee_id, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

Top N with ties

SELECT employee_id, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;

Top N per group (classic)

SELECT *
FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
  FROM employees e
)
WHERE rn <= 3;

Pagination (offset + limit)

SELECT *
FROM employees
ORDER BY employee_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

---

Analytical Functions

Running totals

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) running_total
FROM orders;

Moving average

SELECT order_date, amount,
       AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) avg_7day
FROM daily_sales;

Rank, Dense Rank, Row Number

SELECT employee_id, department_id, salary,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) rnk,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) dense_rnk,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
FROM employees;

Lag and Lead (previous/next row)

SELECT order_date, amount,
       LAG(amount, 1)  OVER (ORDER BY order_date) prev_amount,
       LEAD(amount, 1) OVER (ORDER BY order_date) next_amount,
       amount - LAG(amount, 1) OVER (ORDER BY order_date) change
FROM daily_sales;

NTILE (divide into buckets)

SELECT employee_id, salary,
       NTILE(4) OVER (ORDER BY salary) quartile
FROM employees;

LISTAGG (aggregate strings)

SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) employees
FROM employees
GROUP BY department_id;

---

Hierarchical Queries

Connect By (traditional)

SELECT LEVEL, LPAD(' ', 2 * (LEVEL - 1)) || employee_id AS tree,
       employee_id, manager_id, last_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

Recursive CTE (ANSI standard, 11gR2+)

WITH emp_tree (employee_id, manager_id, last_name, lvl, path) AS (
  SELECT employee_id, manager_id, last_name, 1, CAST(last_name AS VARCHAR2(4000))
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.last_name, t.lvl + 1, t.path || ' > ' || e.last_name
  FROM employees e JOIN emp_tree t ON e.manager_id = t.employee_id
)
SELECT lvl, LPAD(' ', 2 * (lvl - 1)) || last_name AS tree, path
FROM emp_tree
ORDER BY path;

SYS_CONNECT_BY_PATH

SELECT employee_id,
       SYS_CONNECT_BY_PATH(last_name, ' / ') full_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

---

Pivot and Unpivot

PIVOT (rows to columns)

SELECT *
FROM (
  SELECT department_id, job_id, salary
  FROM employees
)
PIVOT (
  SUM(salary) FOR job_id IN ('SA_MAN' AS sales_mgr, 'SA_REP' AS sales_rep, 'IT_PROG' AS it_prog)
);

UNPIVOT (columns to rows)

SELECT *
FROM quarterly_sales
UNPIVOT (
  amount FOR quarter IN (q1_sales AS 'Q1', q2_sales AS 'Q2', q3_sales AS 'Q3', q4_sales AS 'Q4')
);

Cross-tab without PIVOT (works on all versions)

SELECT department_id,
       SUM(CASE WHEN job_id = 'SA_MAN'  THEN salary END) sales_mgr,
       SUM(CASE WHEN job_id = 'SA_REP'  THEN salary END) sales_rep,
       SUM(CASE WHEN job_id = 'IT_PROG' THEN salary END) it_prog
FROM employees
GROUP BY department_id;

---

Date Patterns

Common date operations

-- Current timestamp
SELECT SYSDATE, SYSTIMESTAMP FROM dual;

-- Truncate to day/month/year
SELECT TRUNC(SYSDATE),            -- midnight today
       TRUNC(SYSDATE, 'MM'),      -- first of month
       TRUNC(SYSDATE, 'YYYY'),    -- first of year
       TRUNC(SYSDATE, 'IW')       -- Monday of this week (ISO)
FROM dual;

-- Add intervals
SELECT SYSDATE + 7                 AS next_week,
       ADD_MONTHS(SYSDATE, 3)      AS next_quarter,
       SYSDATE + INTERVAL '4' HOUR AS four_hours_later
FROM dual;

-- Difference between dates
SELECT ROUND(date2 - date1)                AS days_diff,
       ROUND((date2 - date1) * 24)         AS hours_diff,
       MONTHS_BETWEEN(date2, date1)         AS months_diff
FROM dual;

Date ranges

-- All rows from today
WHERE created_date >= TRUNC(SYSDATE)
  AND created_date <  TRUNC(SYSDATE) + 1

-- Last 7 days
WHERE created_date >= SYSDATE - 7

-- Specific month
WHERE created_date >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
  AND created_date <  TO_DATE('2025-02-01', 'YYYY-MM-DD')

Generate date series

SELECT TRUNC(SYSDATE) - LEVEL + 1 AS day_date
FROM dual
CONNECT BY LEVEL <= 30
ORDER BY 1;

---

DBA-Specific Patterns

Find objects by name pattern

SELECT owner, object_name, object_type, status, created
FROM dba_objects
WHERE object_name LIKE UPPER('&pattern') || '%'
ORDER BY owner, object_type, object_name;

Table row counts (all tables in a schema)

SELECT table_name, num_rows, last_analyzed
FROM dba_tables
WHERE owner = UPPER('&schema')
ORDER BY num_rows DESC NULLS LAST;

Find columns across all tables

SELECT owner, table_name, column_name, data_type, data_length
FROM dba_tab_columns
WHERE column_name LIKE UPPER('&column_pattern') || '%'
ORDER BY owner, table_name;

Session kill script generator

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd
FROM v$session
WHERE username = UPPER('&username');

Index usage detection (12c+)

ALTER INDEX owner.index_name MONITORING USAGE;
-- Wait for a representative period, then check:
SELECT * FROM v$object_usage WHERE index_name = 'INDEX_NAME';

Partition pruning check

EXPLAIN PLAN FOR <your query>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for "PARTITION RANGE" or "PARTITION LIST" operations
-- "Pstart" and "Pstop" show which partitions are accessed

---

Conditional Logic

DECODE vs CASE

-- DECODE (Oracle-specific, equality only)
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') status_text FROM table1;

-- CASE (ANSI standard, supports ranges)
SELECT CASE
         WHEN salary >= 100000 THEN 'Executive'
         WHEN salary >= 50000  THEN 'Senior'
         ELSE 'Junior'
       END salary_band
FROM employees;

NVL, NVL2, COALESCE, NULLIF

SELECT NVL(commission_pct, 0),                     -- replace NULL with 0
       NVL2(commission_pct, 'Has Comm', 'No Comm'), -- if not null / if null
       COALESCE(phone, mobile, email, 'N/A'),        -- first non-null
       NULLIF(col1, col2)                             -- NULL if equal
FROM employees;

---

Useful DBA Query Templates

Space usage by schema

SELECT owner, ROUND(SUM(bytes) / 1024 / 1024) size_mb, COUNT(*) segment_count
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'MDSYS', 'CTXSYS', 'XDB')
GROUP BY owner
ORDER BY 2 DESC;

Long-running sessions

SELECT s.sid, s.serial#, s.username, s.status,
       s.last_call_et / 60 minutes_idle,
       s.sql_id, s.event
FROM v$session s
WHERE s.username IS NOT NULL
  AND s.last_call_et > 3600
ORDER BY s.last_call_et DESC;

Table fragmentation estimate

SELECT table_name,
       ROUND(blocks * 8 / 1024) allocated_mb,
       ROUND(num_rows * avg_row_len / 1024 / 1024) actual_mb,
       ROUND(blocks * 8 / 1024) - ROUND(num_rows * avg_row_len / 1024 / 1024) wasted_mb
FROM dba_tables
WHERE owner = UPPER('&schema')
  AND blocks > 0
ORDER BY 4 DESC NULLS LAST;

---

Related Resources

• Performance Analysis Scripts (171 tools) — Production-ready query scripts
• Schema Analysis Scripts (60 tools) — Object and schema queries
• Date Functions Cheat Sheet — Detailed date function reference
• V$ Views Reference — Dynamic performance view guide
• Oracle Performance Tuning Cheat Sheet — Tuning parameters and shortcuts