Uncover Oracle’s Evolving SQL Commands: From Version 6 to 12c

Oracle Core Concepts

Oracle databases are built around a set of schema objects:

Table : the basic unit of data storage, organized in rows and columns.

Index : an optional structure that speeds data retrieval by providing fast access paths to rows.

Sequence : generates a globally unique series of numbers, typically used for surrogate keys.

View : a virtual table that presents selected rows and columns from one or more base tables without storing data itself.

Cluster : groups related tables physically together in the same data blocks to reduce I/O for multi‑table joins.

Tablespace : a logical storage container that aggregates one or more data files, separating logical schema objects from physical file locations.

Database link : a named object that defines a communication path to a remote Oracle database, enabling distributed queries.

Synonym : an alias for a schema object that provides location transparency and data‑independence for applications.

SQL Command Classification in Oracle 6

Oracle groups SQL statements into six functional categories. In version 6 the first five categories are present; the sixth—System Control—appears starting with version 7.

Data Definition Language (DDL)

DDL commands create, modify, or remove schema objects and manage metadata. Oracle 6 defines 27 distinct DDL statements, including the most frequently used ones: CREATE – create tables, indexes, clusters, synonyms, etc. ALTER – modify the definition of existing objects. DROP – remove objects. GRANT – give privileges or roles to users. REVOKE – withdraw previously granted privileges. ANALYZE – collect statistics on tables, indexes, or clusters. AUDIT / NOAUDIT – enable or disable traditional auditing. COMMENT – attach descriptive text to dictionary objects. RENAME – rename an existing object.

Data Manipulation Language (DML)

INSERT

– add new rows to a table. UPDATE – modify column values of existing rows. DELETE – remove rows from a table. SELECT – retrieve rows from one or more tables. LOCK TABLE – explicitly lock a table or view. EXPLAIN PLAN – display the optimizer's estimated execution plan for a query.

Transaction Control Commands

COMMIT

– make all changes in the current transaction permanent. ROLLBACK – undo changes made in the current transaction. SAVEPOINT – define a point within a transaction to which a later ROLLBACK can return. SET TRANSACTION – specify transaction characteristics such as isolation level.

Session Control Commands

ALTER SESSION

– modify session‑level parameters (e.g., NLS settings, optimizer mode).

Embedded SQL

Oracle 6 also supports embedding SQL statements within host languages, though specific commands are not enumerated here.

New SQL Commands Introduced in Later Oracle Versions

Each major release after version 6 added new concepts and associated SQL statements. The table below summarizes the key additions and the approximate number of new commands.

Version 7 – introduced procedural SQL, triggers, security management, object management, and system‑control statements (≈30 new commands).

Version 8 – added directories, libraries, materialized views, and user‑defined types (18 new commands).

Version 8i – added contexts, dimensions, Java integration, and extended statistics (18 new commands).

Version 9i – introduced the server parameter file (SPFILE) concept (3 new commands).

Version 10g – added Automatic Storage Management (ASM) disk groups and Flashback Database capabilities (9 new commands).

Version 11g – added Flashback Data Archive and multi‑version schema objects (7 new commands).

Version 12c – introduced pluggable databases (multitenant architecture) and security audit policies for SaaS (10 new commands).