Oracle Database – Introduction | Love Me Some Coding

Oracle Database is the relational database you meet the moment you work on anything old and important — core banking, telecom billing, insurance, ERP, government systems. It has been shipping since 1979, and a surprising amount of the world's transactional data still lives in it.

If you come from MySQL or Postgres, most of your SQL transfers directly. What trips people up is everything around the SQL: a schema is a user, there is no USE database, a DATE carries a time, an empty string is NULL, and nothing is committed until you say so. This post lays out the vocabulary so the rest of the track makes sense.

Instance vs database

Oracle draws a hard line between the two words, and error messages assume you know the difference.

Database — the files on disk: datafiles, control files, redo logs.
Instance — the memory and processes that open those files: the SGA plus a set of background processes.

You connect to an instance; the instance reads the database. One database with several instances in front of it is Real Application Clusters (RAC).

The memory side is the SGA (System Global Area), shared by every session, plus a private PGA per session. Inside the SGA the two areas worth knowing early are the buffer cache (data blocks) and the shared pool (parsed SQL — the reason bind variables matter, see the indexes post).

The background processes you will see named in logs and traces:

Process	Job
`DBWn`	Writes dirty buffers from the cache to datafiles.
`LGWR`	Writes redo to the online redo logs. A `COMMIT` waits on this.
`CKPT`	Signals checkpoints and updates file headers.
`SMON`	Instance recovery, temp cleanup, coalescing free space.
`PMON`	Cleans up after failed sessions, releases their locks.
`ARCn`	Copies filled redo logs to the archive, if archivelog mode is on.

How storage is layered

Every object you create lands somewhere in this hierarchy, and the whole thing is worth memorising because ORA-01653: unable to extend table only makes sense once you have.

Database
 └── Tablespace          logical storage, e.g. USERS, SYSTEM, TEMP, UNDOTBS1
      └── Datafile       an actual file on disk (or ASM volume)
           └── Segment   one per table, index, or LOB
                └── Extent    a contiguous run of blocks
                     └── Block    the smallest unit of I/O, usually 8 KB

Two tablespaces do special work:

UNDO holds the previous version of every changed row. It powers rollback and read consistency — this is Oracle's MVCC, and it is why readers never block writers.
TEMP holds sort and hash workareas that spill out of memory.

Multitenant: CDB and PDB

Since 12c an Oracle database is a container database (CDB) holding one or more pluggable databases (PDB). The CDB root owns the Oracle-supplied metadata; your tables live in a PDB.

This is the single most common reason a first connection fails. You connect to the root, run CREATE USER app_user, and get:

ORA-65096: invalid common user or role name

because in the root every user must be a common user named C##something. The fix is to connect to the PDB instead, which is what the connection examples in this track always do.

-- Where am I?
SELECT sys_context('USERENV', 'CON_NAME') AS container FROM dual;

-- List the pluggable databases (from the root)
SELECT name, open_mode FROM v$pdbs;

-- Switch container (needs elevated privilege)
ALTER SESSION SET CONTAINER = freepdb1;

Editions, and which one to install

Edition	What it is
Free	Free to use, including in production. Capped (a few CPUs, ~12 GB of user data). Replaced Express Edition (XE). Use this to learn.
Standard Edition 2	Licensed, socket-capped, no partitioning, no parallel query.
Enterprise Edition	Everything, plus separately licensed options (Partitioning, RAC, Advanced Compression, Active Data Guard…). Assume nothing is included.
Autonomous Database	Managed Oracle on OCI. Patching, tuning and backups are handled; you get a wallet instead of a host and port.

Version names have drifted over the years — 8i (internet), 9i, 10g/11g (grid), 12c/18c/19c/21c (cloud), and now 23ai. Two matter in practice: 19c is the long-term-support release most enterprises are standardised on, and 23ai is the current one, where you get real BOOLEAN, VECTOR columns and SQL that finally accepts GROUP BY aliases.

Coming from Postgres or MySQL

Read this table before you write your first statement. Each row has cost somebody a whole afternoon.

Oracle	What you probably expected
A user is a schema. `CREATE USER` creates the namespace.	`CREATE DATABASE` / `CREATE SCHEMA`
No `USE db`. You switch with `ALTER SESSION SET CURRENT_SCHEMA`, or qualify names.	`USE mydb;`
`VARCHAR2` is the string type. `VARCHAR` is a synonym Oracle asks you not to use.	`VARCHAR`/`TEXT`
`''` is `NULL`. `WHERE name = ''` matches nothing, ever.	Empty string ≠ NULL
`DATE` includes hours, minutes and seconds. Use `TIMESTAMP` for sub-second.	`DATE` = calendar day only
`SELECT` needs a table, so scalar queries use `FROM dual`.	`SELECT 1;`
DDL commits your open transaction, silently.	Transactional DDL
No `LIMIT`. Use `FETCH FIRST n ROWS ONLY` (12c+) or `ROWNUM`.	`LIMIT 10`
`PL/SQL` is a full procedural language compiled into the database.	Stored procedures as an afterthought

The data dictionary

Oracle has no SHOW TABLES. It has views, in three prefixes, and knowing the pattern replaces most of the documentation:

USER_* — objects you own.
ALL_* — objects you can access, whoever owns them.
DBA_* — everything in the database. Needs privilege.

SELECT table_name FROM user_tables ORDER BY table_name;
SELECT column_name, data_type, nullable FROM user_tab_columns WHERE table_name = 'ORDERS';
SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'ORDERS';
SELECT * FROM v$version;

Note 'ORDERS' in capitals. Unquoted identifiers are folded to uppercase, so that is how they are stored — and the dictionary is case-sensitive about it.

Nothing above sinks in without a database to type into, and you can have one running in about ten minutes. That is the next post: Oracle Database Free in Docker.

Oracle Database – Introduction