Oracle Database Cheat Sheet

Updated 2026-04-29

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

🧠Study flashcards on this topic219 cards · spaced repetition→

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, 219 flashcards. 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

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

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.
TCL (Transaction Control Language)	`COMMIT` `ROLLBACK` `SAVEPOINT sp1` `ROLLBACK TO sp1`	• Manages transaction boundaries • COMMIT makes changes permanent, ROLLBACK undoes them.
DQL (Data Query Language)	`SELECT e.name, d.name` `FROM emp e JOIN dept d ON e.dept_id = d.id` `WHERE e.salary > 5000` `ORDER BY e.name`	• Sometimes classified separately — the SELECT statement that queries data • logically belongs to DML.

Table 2: Data Types

Picking the right column type is the first design decision in any schema, and Oracle gives you a deliberately small core — NUMBER for anything numeric, VARCHAR2 for text, DATE/TIMESTAMP for time — plus large-object and binary types for the heavy stuff. The newer additions like BOOLEAN and VECTOR show where the database is heading, with native true/false columns and AI embeddings now living right alongside the classics.

Type	Example	Description
NUMBER	`salary NUMBER(10,2)` `count NUMBER`	Most versatile numeric type — supports decimals and integers with optional precision and scale.

The rest of this section is part of the full cheat sheet

Table 3: Constraints

Constraints are how you push data rules down into the database itself, so a bad row is rejected no matter which application tries to write it. These six cover the vast majority of real schemas — keys to identify and link rows, and NOT NULL/CHECK/DEFAULT to keep individual values sane.

Constraint	Example	Description
PRIMARY KEY	`id NUMBER PRIMARY KEY` `CONSTRAINT pk_emp PRIMARY KEY (emp_id)`	Uniquely identifies each row — automatically creates unique index and enforces NOT NULL.

The rest of this section is part of the full cheat sheet

Table 4: Indexes

The right index turns a full table scan into an instant lookup, but the wrong choice can quietly slow down every insert. The key distinction running through this list is cardinality — B-tree indexes shine on high-cardinality columns like names or IDs, while bitmap indexes win on low-cardinality flags, and the specialized variants (function-based, invisible, IOT) each solve a narrower problem.

Type	Example	Description
B-tree Index	`CREATE INDEX idx_emp_name ON emp(last_name)`	• Default index type — ideal for high-cardinality columns • supports range scans and equality lookups.

The rest of this section is part of the full cheat sheet

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.
TCL (Transaction Control Language)	`COMMIT` `ROLLBACK` `SAVEPOINT sp1` `ROLLBACK TO sp1`	• Manages transaction boundaries • COMMIT makes changes permanent, ROLLBACK undoes them.
DQL (Data Query Language)	`SELECT e.name, d.name` `FROM emp e JOIN dept d ON e.dept_id = d.id` `WHERE e.salary > 5000` `ORDER BY e.name`	• Sometimes classified separately — the SELECT statement that queries data • logically belongs to DML.

Table 2: Data Types

Type	Example	Description
NUMBER	`salary NUMBER(10,2)` `count NUMBER`	Most versatile numeric type — supports decimals and integers with optional precision and scale.

Table 3: Constraints

Constraint	Example	Description
PRIMARY KEY	`id NUMBER PRIMARY KEY` `CONSTRAINT pk_emp PRIMARY KEY (emp_id)`	Uniquely identifies each row — automatically creates unique index and enforces NOT NULL.

Table 4: Indexes

Type	Example	Description
B-tree Index	`CREATE INDEX idx_emp_name ON emp(last_name)`	• Default index type — ideal for high-cardinality columns • supports range scans and equality lookups.