Oracle Database Cheat Sheet

Updated 2026-04-29

Next Topic: pgvector and PostgreSQL for AI Vector Search Cheat Sheet

Oracle Database is a multi-model relational database management system developed by Oracle Corporation, widely deployed in enterprise environments for mission-critical applications requiring high availability, data integrity, and scalability. As the market-leading commercial database, Oracle excels at handling massive transaction volumes, complex queries, and multi-terabyte datasets while providing advanced features like Real Application Clusters (RAC), Data Guard for disaster recovery, and comprehensive PL/SQL procedural programming. The latest release, Oracle AI Database 26ai (October 2025), adds native AI vector search, the BOOLEAN and VECTOR SQL data types, JSON-Relational Duality views, and dozens of SQL/PL/SQL productivity enhancements on top of the 23ai long-term support foundation. Whether managing legacy systems or building modern AI-enabled applications, mastering Oracle's architecture — from tablespaces and indexes to execution plans and partitioning — is essential for DBAs, developers, and data engineers.

What This Cheat Sheet Covers

This topic spans 40 focused tables and 250 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: SQL Statement CategoriesTable 2: Data TypesTable 3: ConstraintsTable 4: IndexesTable 5: JoinsTable 6: Set OperationsTable 7: SubqueriesTable 8: Aggregate FunctionsTable 9: Window FunctionsTable 10: Hierarchical QueriesTable 11: PIVOT and UNPIVOTTable 12: MERGE StatementTable 13: Common Table Expressions (CTEs)Table 14: JSON SupportTable 15: Regular ExpressionsTable 16: PL/SQL Control StructuresTable 17: CursorsTable 18: Exception HandlingTable 19: PL/SQL CollectionsTable 20: Procedures and FunctionsTable 21: Dynamic SQLTable 22: Join Methods (Execution Plan)Table 23: TransactionsTable 24: Transaction Isolation LevelsTable 25: LockingTable 26: ViewsTable 27: Sequences and SynonymsTable 28: TriggersTable 29: TablespacesTable 30: PartitioningTable 31: Backup and RecoveryTable 32: High AvailabilityTable 33: Performance TuningTable 34: Parallel ExecutionTable 35: CompressionTable 36: SecurityTable 37: SQL*Plus CommandsTable 38: Common DBMS PackagesTable 39: Advanced Queuing (AQ)Table 40: Oracle 23ai / 26ai New Features

Table 1: SQL Statement Categories

Every statement you write in Oracle falls into one of these families, and knowing which is which explains a lot of surprising behavior — like why a CREATE TABLE can't be rolled back but a DELETE can. DML touches the rows, DDL shapes the objects that hold them, DCL decides who's allowed in, and TCL draws the boundaries of a transaction.

Category	Example	Description
DML (Data Manipulation Language)	`SELECT * FROM employees` `INSERT INTO dept VALUES (10, 'IT')` `UPDATE emp SET salary = 5000` `DELETE FROM emp WHERE id = 1`	Retrieves, inserts, updates, or deletes data in tables — most common SQL operations for working with data rows.
DDL (Data Definition Language)	`CREATE TABLE t (id NUMBER)` `ALTER TABLE t ADD col VARCHAR2(50)` `DROP INDEX idx_name` `TRUNCATE TABLE t`	• Defines, modifies, or removes database schema objects • auto-commits immediately and cannot be rolled back.
DCL (Data Control Language)	`GRANT SELECT ON emp TO user1` `REVOKE INSERT ON dept FROM user2`	Controls user access and privileges — manages who can do what on database objects.

Table 1: SQL Statement Categories

Category	Example	Description
DML (Data Manipulation Language)	`SELECT * FROM employees` `INSERT INTO dept VALUES (10, 'IT')` `UPDATE emp SET salary = 5000` `DELETE FROM emp WHERE id = 1`	Retrieves, inserts, updates, or deletes data in tables — most common SQL operations for working with data rows.
DDL (Data Definition Language)	`CREATE TABLE t (id NUMBER)` `ALTER TABLE t ADD col VARCHAR2(50)` `DROP INDEX idx_name` `TRUNCATE TABLE t`	• Defines, modifies, or removes database schema objects • auto-commits immediately and cannot be rolled back.
DCL (Data Control Language)	`GRANT SELECT ON emp TO user1` `REVOKE INSERT ON dept FROM user2`	Controls user access and privileges — manages who can do what on database objects.