Oracle Database — Overview

What is Oracle Database?

Oracle Database is the world's leading enterprise relational database management system (RDBMS), used by Fortune 500 companies, banks, telecoms, and governments. Known for: enterprise features, RAC (Real Application Clusters) for extreme HA, advanced security, and decades of stability.

Oracle vs PostgreSQL vs MySQL

|---|---|---|---|

When You'll Encounter Oracle

Oracle Architecture

Instance: the memory + background processes (PMON, SMON, DBWn, LGWR, CKPT)
Database: the physical files on disk (datafiles, redo logs, control files)

SGA (System Global Area):
  Buffer Cache     → caches data blocks in memory
  Shared Pool      → caches parsed SQL statements
  Redo Log Buffer  → log of changes before writing to disk
  
PGA (Program Global Area):
  Private to each session
  Sort area, hash join area

Key Oracle-Specific Concepts

Tablespace — logical storage unit grouping related segments. Like a filesystem folder for database objects.

Sequence — auto-incrementing number generator. In PostgreSQL, BIGSERIAL does this automatically; in Oracle, you create sequences explicitly.

PL/SQL — Oracle's procedural extension to SQL for stored procedures, functions, triggers, and packages.

RAC (Real Application Clusters) — multiple Oracle instances sharing the same storage. Provides extreme HA — if one node fails, the database as a whole stays up and surviving nodes keep serving traffic, though sessions connected to the failed node still need to reconnect/fail over (not literally zero-impact for those specific sessions).

ASM (Automatic Storage Management) — Oracle's built-in volume manager for database storage.

Getting Started with Oracle

Free Options:

(needs verification — exact XE version and its specific CPU/RAM/storage limits, and Free Tier storage allowance, change between Oracle releases; confirm current values on Oracle's own download/Cloud Free Tier pages)

# Docker (quickest for learning)
docker pull gvenzl/oracle-xe:21-slim
docker run -d --name oracle-xe \
  -e ORACLE_PASSWORD=MyPassword \
  -p 1521:1521 \
  gvenzl/oracle-xe:21-slim

# Connect
docker exec -it oracle-xe sqlplus system/MyPassword@//localhost:1521/XEPDB1

Oracle SQL Basics

-- Oracle uses DUAL table for expressions without a real table
SELECT SYSDATE FROM DUAL;          -- Current date/time
SELECT 2 + 2 FROM DUAL;            -- Returns 4
SELECT USER FROM DUAL;             -- Current user

-- Sequences (no SERIAL/AUTO_INCREMENT)
CREATE SEQUENCE users_seq START WITH 1 INCREMENT BY 1;
INSERT INTO users (id, name) VALUES (users_seq.NEXTVAL, 'Alice');

-- Rownum (old) vs ROW_NUMBER() (modern)
SELECT * FROM users WHERE ROWNUM <= 10;   -- Old way
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM users) WHERE rn <= 10;

Course Sections