Oracle Database

Oracle Database is one of the most widely deployed relational databases in large enterprises, running core banking, telecommunications billing, ERP, government, and healthcare systems. It is known for strong consistency guarantees, mature tooling for backup, recovery, and high availability, sophisticated optimization, and a rich procedural language, PL/SQL. It runs on premises, on Oracle's Exadata engineered systems, in Oracle Cloud Infrastructure — including the self-managing Autonomous Database — and inside other clouds.

It is also a database where edition and licensing decisions shape what you can use: many familiar features, such as partitioning, diagnostic and tuning packs, and clustering, are separately licensed options. Engineers moving between Oracle and other databases need to know both the technical behavior and these constraints.

TL;DR

Quick Example

A query using a bind variable, with the actual execution plan of its last run:

The plan output shows each operation with estimated and actual row counts; large differences between them usually point to stale or missing statistics.

A set-based update instead of a row-by-row loop, followed by recovering a mistake with Flashback Query:

Core Concepts

Editions, Deployment, and Licensing

Options and packs — Partitioning, Real Application Clusters, Advanced Security, and the Diagnostics and Tuning Packs (which cover AWR, ASH, and SQL Monitor) — are licensed separately, and licensing rules for virtualization and cloud differ by environment. Confirm entitlements before relying on a feature; querying licensed views without a license can create compliance exposure.

Architecture

An instance (memory structures — the SGA and PGA — and background processes) serves a database (data files, control files, and online redo logs). In the multitenant architecture, a container database hosts many pluggable databases, each appearing to applications as a separate database while sharing memory and background processes; non-CDB architecture is no longer supported in recent releases. Data is stored in tablespaces; undo tablespaces hold before-images used for rollback and read consistency.

SQL, PL/SQL, and Data Types

PL/SQL adds procedural logic — packages, procedures, functions, triggers, exception handling — that runs inside the database, with bulk operations (BULK COLLECT, FORALL) for row-by-row processing at scale. Key data type behavior:

Execution Plans, Bind Variables, and Statistics

The cost-based optimizer chooses plans from optimizer statistics gathered with DBMS_STATS (automatically by default maintenance windows). Bind variables let one parsed statement serve many values, avoiding hard parses that waste CPU and shared pool memory; literal SQL in loops is a classic performance problem. Diagnose with EXPLAIN PLAN, DBMS_XPLAN.DISPLAY_CURSOR, and — if licensed — AWR reports and SQL Monitor. See Query Optimization.

Read Consistency and Locking

Oracle gives each query a consistent snapshot as of its start using undo data, so readers do not block writers and writers do not block readers. Long-running queries can fail with ORA-01555 "snapshot too old" if the undo they need has been overwritten; size undo retention for the longest queries. Row-level locks are taken by DML and SELECT ... FOR UPDATE.

Backup, Recovery, and High Availability

A minimal RMAN routine:

Test full restores regularly. See Database Backups.

Oracle Compared With PostgreSQL

Migrations from Oracle to PostgreSQL are common; they require translating PL/SQL, data types, sequences, and NULL semantics, and retesting performance. See PostgreSQL.

Best Practices

Always Use Bind Variables

Parameterize application SQL to share plans and prevent SQL injection. See SQL.

Think in Sets

Prefer single SQL statements over row-by-row PL/SQL loops; use bulk operations when procedural processing is unavoidable.

Keep Statistics Current

Let automatic statistics jobs run, gather statistics after large data changes, and investigate plans where estimates and actual row counts diverge.

Know Your Licenses

Track which options and packs are licensed, disable or restrict access to unlicensed features, and review licensing before changing virtualization or cloud deployments.

Common Mistakes

Literal SQL in Loops

Relying on Empty Strings

Running Production Without ARCHIVELOG

FAQ

What is Oracle Database used for?

Mission-critical transactional and analytical workloads in large organizations — banking, telecom, ERP, government, healthcare — where strong consistency, high availability, and mature operational tooling matter.

What is the difference between a CDB and a PDB?

A container database (CDB) is the overall database with shared memory and processes; pluggable databases (PDBs) are self-contained databases inside it that applications connect to, which can be cloned, moved, and upgraded independently.

Why does Oracle treat empty strings as NULL?

It is historical Oracle behavior retained for compatibility. Code ported from other databases must handle it, for example by checking IS NULL instead of comparing to ''.

How do I read an Oracle execution plan?

Use DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST (after running the statement with plan statistics) to see each operation, estimated rows, and actual rows. Start with operations where estimates and actuals differ most.

Is Oracle Database free?

Free editions exist for development and small workloads with resource limits. Production use of Standard or Enterprise Edition, and of most options and packs, requires paid licenses.

Related Topics

References