← All Cheat Codes
Library / Cheat Codes / Oracle Database
ðī Oracle Database Cheat Codes
Oracle Database Cheat Codes
Oracle Cheat Codes for SGA/PGA memory, PL/SQL, flashback queries, and enterprise tuning. 20 essential cheat codes ready to copy and execute.
# 1 SELECT DQL
Retrieve columns from Oracle table.
Syntax:
SELECT col1, col2 FROM tbl; Working Example:
SELECT employee_id, last_name FROM employees; # 2 WHERE DQL
Filter row results.
Syntax:
SELECT * FROM tbl WHERE col = val; Working Example:
SELECT * FROM employees WHERE department_id = 10; # 3 JOIN Joins
Match records across joined tables.
Syntax:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk; Working Example:
SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; # 4 GROUP BY Aggregations
Group rows for calculation.
Syntax:
SELECT col, COUNT(*) FROM tbl GROUP BY col; Working Example:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id; # 5 HAVING Aggregations
Filter aggregated group calculations.
Syntax:
SELECT col, AVG(sal) FROM tbl GROUP BY col HAVING AVG(sal) > 5000; Working Example:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000; # 6 INSERT INTO DML
Add new row into Oracle table.
Syntax:
INSERT INTO tbl (col) VALUES (val); Working Example:
INSERT INTO departments (department_id, department_name) VALUES (90, 'Executive'); # 7 UPDATE SET DML
Modify column values.
Syntax:
UPDATE tbl SET col = val WHERE condition; Working Example:
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 20; # 8 DELETE FROM DML
Remove records matching filter.
Syntax:
DELETE FROM tbl WHERE condition; Working Example:
DELETE FROM job_history WHERE end_date < '2020-01-01'; # 9 MERGE INTO (UPSERT) DML
ANSI standard MERGE upsert statement.
Syntax:
MERGE INTO target t USING source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.val = s.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val); Working Example:
MERGE INTO emp_target t USING emp_stage s ON (t.emp_id = s.emp_id) WHEN MATCHED THEN UPDATE SET t.sal = s.sal WHEN NOT MATCHED THEN INSERT (emp_id, sal) VALUES (s.emp_id, s.sal); # 10 NVL / NVL2 Functions
Replace NULL values with default fallback.
Syntax:
SELECT NVL(col, default_val) FROM tbl; Working Example:
SELECT last_name, NVL(commission_pct, 0) FROM employees; # 11 FETCH FIRST N ROWS Pagination
Standard ANSI pagination syntax in Oracle 12c+.
Syntax:
SELECT * FROM tbl ORDER BY col DESC FETCH FIRST n ROWS ONLY; Working Example:
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY; # 12 LISTAGG Aggregations
Concatenate string values across rows.
Syntax:
SELECT LISTAGG(col, '; ') WITHIN GROUP (ORDER BY col) FROM tbl GROUP BY grp; Working Example:
SELECT department_id, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) FROM employees GROUP BY department_id; # 13 ROWNUM Pagination
Legacy pseudo-column row limiter.
Syntax:
SELECT * FROM tbl WHERE ROWNUM <= n; Working Example:
SELECT * FROM employees WHERE ROWNUM <= 5; # 14 START WITH CONNECT BY Hierarchical
Traverse parent-child org hierarchies.
Syntax:
SELECT LEVEL, col FROM tbl START WITH parent IS NULL CONNECT BY PRIOR id = parent; Working Example:
SELECT LEVEL, employee_id, last_name FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; # 15 CREATE SEQUENCE Sequences
Build sequence object for auto keys.
Syntax:
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1; Working Example:
CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 1; # 16 NEXTVAL / CURRVAL Sequences
Fetch next unique value from sequence.
Syntax:
SELECT seq_name.NEXTVAL FROM dual; Working Example:
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'Alex'); # 17 FLASHBACK QUERY Recovery
Query past historical data state.
Syntax:
SELECT * FROM tbl AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE); Working Example:
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE); # 18 PIVOT Analytics
Transform rows into reporting columns.
Syntax:
SELECT * FROM (SELECT grp, col, val FROM tbl) PIVOT (SUM(val) FOR col IN ('A', 'B')); Working Example:
SELECT * FROM (SELECT department_id, salary FROM employees) PIVOT (SUM(salary) FOR department_id IN (10, 20, 30)); # 19 CREATE MATERIALIZED VIEW Views
Create pre-computed query snapshot view.
Syntax:
CREATE MATERIALIZED VIEW mv_name AS SELECT ...; Working Example:
CREATE MATERIALIZED VIEW dept_summary AS SELECT department_id, COUNT(*) cnt FROM employees GROUP BY department_id; # 20 EXPLAIN PLAN FOR Performance
Generate optimizer plan into plan_table.
Syntax:
EXPLAIN PLAN FOR SELECT * FROM tbl WHERE col = val; Working Example:
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 50; No cheat codes found matching your search term.