Oracle SQL Cheat Sheet
Oracle SQL and PL/SQL fundamentals covering SQL*Plus, data types, sequences, ROWNUM pagination, and stored procedures.
2 PagesAdvancedMar 5, 2026
SQL*Plus Basics
Connecting and exploring schema objects.
sql
sqlplus username/password@//host:1521/service_nameSELECT table_name FROM user_tables;DESCRIBE employees;SET LINESIZE 200SET PAGESIZE 50EXIT;
DDL & DML
Creating tables and manipulating rows.
sql
CREATE TABLE employees ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL, hire_date DATE DEFAULT SYSDATE);INSERT INTO employees (name) VALUES ('Alice');SELECT * FROM employees WHERE ROWNUM <= 10;UPDATE employees SET name = 'Alice B.' WHERE id = 1;COMMIT;
PL/SQL Basics
Procedural blocks and stored procedures.
sql
BEGIN FOR emp IN (SELECT id, name FROM employees) LOOP DBMS_OUTPUT.PUT_LINE(emp.name); END LOOP;END;/CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER, p_pct NUMBER) ISBEGIN UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE id = p_id; COMMIT;END;/
Core Concepts & Types
Oracle-specific data types and objects.
- NUMBER(p,s)- Oracle's universal numeric type with precision and scale
- VARCHAR2(n)- variable-length string, preferred over the fixed-length CHAR
- SYSDATE / SYSTIMESTAMP- functions returning the current date/time
- ROWNUM- pseudo-column numbering result rows before ORDER BY is applied
- Sequence- CREATE SEQUENCE seq_name for generating unique numbers
- Synonym- an alias for a table or view, often used for cross-schema access
Analytic (Window) Functions
Ranking and running aggregates with PARTITION BY / OVER, distinct from GROUP BY.
sql
SELECT dept_id, emp_name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank, LAG(salary, 1) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS prev_salary, LEAD(salary, 1) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS next_salary, SUM(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_totalFROM employees;-- Top-N per group without a subquerySELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees e) WHERE rn <= 3;
Hierarchical Queries (CONNECT BY)
Oracle's native syntax for walking tree-structured data, e.g. org charts.
sql
SELECT LPAD(' ', 2 * (LEVEL - 1)) || employee_name AS org_chart, LEVEL, CONNECT_BY_ROOT employee_name AS top_manager, SYS_CONNECT_BY_PATH(employee_name, '/') AS pathFROM employeesSTART WITH manager_id IS NULLCONNECT BY PRIOR employee_id = manager_idORDER SIBLINGS BY employee_name;-- Detect cycles safelySELECT employee_id, employee_nameFROM employeesCONNECT BY NOCYCLE PRIOR employee_id = manager_id;
PL/SQL: Cursors, BULK COLLECT & Exceptions
Set-based row processing and structured exception handling for high-volume PL/SQL.
sql
DECLARE TYPE emp_tab IS TABLE OF employees%ROWTYPE; l_emps emp_tab; CURSOR c_emps IS SELECT * FROM employees WHERE dept_id = 10;BEGIN OPEN c_emps; LOOP FETCH c_emps BULK COLLECT INTO l_emps LIMIT 500; FORALL i IN 1 .. l_emps.COUNT SAVE EXCEPTIONS UPDATE employees SET salary = l_emps(i).salary * 1.05 WHERE employee_id = l_emps(i).employee_id; EXIT WHEN c_emps%NOTFOUND; END LOOP; CLOSE c_emps;EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Duplicate key encountered'); WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, 'Bulk update failed: ' || SQLERRM);END;/
Table Partitioning
Range, list, and composite partitioning for large tables and partition pruning.
sql
CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, region VARCHAR2(20), amount NUMBER)PARTITION BY RANGE (sale_date)SUBPARTITION BY LIST (region)SUBPARTITION TEMPLATE ( SUBPARTITION p_us VALUES ('US'), SUBPARTITION p_eu VALUES ('EU'), SUBPARTITION p_other VALUES (DEFAULT)) ( PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01'), PARTITION p_2026 VALUES LESS THAN (DATE '2027-01-01'));-- Partition pruning happens automatically when the WHERE clause matches the keySELECT * FROM sales WHERE sale_date >= DATE '2026-01-01' AND region = 'US';ALTER TABLE sales ADD PARTITION p_2027 VALUES LESS THAN (DATE '2028-01-01');
Performance Diagnostics
Tools and concepts for tuning beyond a basic EXPLAIN PLAN.
- Bind variables- use `:id` placeholders so the optimizer reuses cursors (cursor_sharing) instead of hard-parsing every literal
- AWR report- DBA_HIST snapshots summarizing top SQL, wait events, and load over a time window
- DBMS_XPLAN.DISPLAY_CURSOR- shows the actual execution plan with real row counts, not just estimates
- Hint /*+ INDEX(t idx) */- forces the optimizer to use a specific index when statistics mislead it
- Wait event 'db file sequential read'- single-block I/O, typically an index lookup; excessive counts suggest a missing index
- Adaptive Cursor Sharing- lets Oracle generate multiple plans for one SQL_ID when bind values have skewed selectivity
- STATISTICS_LEVEL=ALL- hint or session setting that enables actual row-count tracking for DBMS_XPLAN
Pro Tip
Filtering with ROWNUM before ORDER BY gives unpredictable "top N" results — wrap the query in a subquery, apply ORDER BY there, then filter ROWNUM on the outer query (or use FETCH FIRST n ROWS ONLY on 12c+).
Was this cheat sheet helpful?
Explore Topics
#OracleSQL#OracleSQLCheatSheet#Database#Advanced#SQLPlusBasics#DDLDML#PLSQLBasics#CoreConceptsTypes#Databases#CheatSheet#SkillVeris
