Introduction to Oracle Database

Memory

• SGA (System Global Area) is shared by all sessions. Its main parts are the buffer cache (copies of data blocks), the shared pool (parsed SQL and dictionary data) and the redo log buffer.
• PGA (Program Global Area) is private to each server process and is used for sorts, hash joins and session variables.

Background processes

A committed transaction is durable because its redo is on disk, even if the changed data blocks have not been written yet. After a crash, the instance replays redo to recover them.

Physical storage

Lose all copies of the control file or the current redo log and recovery becomes difficult, which is why Oracle lets you multiplex both across disks.

Logical storage

Oracle places a logical layer between your tables and the files.

• A tablespace is a named storage container made of one or more data files. SYSTEM and SYSAUX hold Oracle's own metadata, UNDO holds undo data, TEMP holds sort spill, and USERS is the default for application data.
• A segment is the storage for one object such as a table, an index or a partition.
• An extent is a contiguous run of blocks allocated to a segment.
• A data block is the smallest unit of I/O, 8 KB by default.

So a table is a segment, made of extents, made of blocks, stored in a tablespace, which lives in data files.

Users, schemas and the data dictionary

In Oracle a schema is the set of objects owned by a user, and the two share a name. Creating the user hr creates the schema hr. There is no separate CREATE DATABASE step per application as in MySQL; you create a user.

The data dictionary is a set of read-only views describing everything in the database. They come in three scopes:

• USER_*: objects you own (USER_TABLES).
• ALL_*: objects you can access (ALL_TABLES).
• DBA_*: everything, for privileged users (DBA_TABLES).

V$ views such as V$SESSION and V$INSTANCE expose live instance state.

The multitenant architecture

A current Oracle database is a container database (CDB) that holds one or more pluggable databases (PDBs). The CDB owns the instance, memory and redo. Each PDB has its own users, tablespaces and service name and looks like a standalone database to applications. The older single-database (non-CDB) layout is no longer supported in current releases.

The practical consequence: you connect to a PDB's service name, not to the CDB root, and you create application users inside a PDB.

Transactions and read consistency

• A transaction starts implicitly with your first DML statement and ends with COMMIT or ROLLBACK. There is no autocommit on the server; some client tools add it.
• DDL statements such as CREATE TABLE commit implicitly, before and after they run.
• Before a block is changed, the old values are written to undo. Queries use undo to see the data as it was when the query started, so readers never block writers and writers never block readers.
• The default isolation level is READ COMMITTED. SERIALIZABLE is also available.

Try it

The fastest route to a working database is the Free edition container image:

docker run -d --name oracle-free -p 1521:1521 \
  -e ORACLE_PWD="Str0ng#Passw0rd" \
  container-registry.oracle.com/database/free:latest

Wait until docker logs oracle-free reports that the database is ready, then connect to the default PDB, FREEPDB1, with SQL*Plus or SQLcl:

sqlplus system@//localhost:1521/FREEPDB1

A few queries show the concepts above:

SELECT instance_name, version_full, status FROM v$instance;

SELECT name, open_mode FROM v$pdbs;

SELECT tablespace_name, contents FROM dba_tablespaces;

SELECT file_name, tablespace_name FROM dba_data_files;

Create a schema and a table to work with:

CREATE USER demo IDENTIFIED BY "Dem0#Passw0rd"
  DEFAULT TABLESPACE users QUOTA 100M ON users;

GRANT CREATE SESSION, CREATE TABLE TO demo;

CREATE TABLE demo.departments (
  department_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  department_name VARCHAR2(50) NOT NULL
);

INSERT INTO demo.departments (department_name) VALUES ('Finance');
COMMIT;

SELECT * FROM demo.departments;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ---------------
            1 Finance

Tools

• SQL*Plus: the classic command-line client, installed with every database.
• SQLcl: a newer command-line client with history, formatting and scripting improvements.
• SQL Developer: the free graphical client, also available as a Visual Studio Code extension.
• RMAN: backup and recovery.
• Data Pump (expdp, impdp): logical export and import.

Things that surprise people coming from other databases

• An empty string '' is NULL in Oracle. WHERE name = '' never matches.
• DATE always includes a time of day.
• VARCHAR2, not VARCHAR, is the standard string type.
• Unquoted identifiers are stored in upper case. Quoting a name ("MyTable") makes it case-sensitive forever.
• Before 23ai, every SELECT needed a FROM clause, which is why older code selects from the DUAL table.
• ORA-12514 and ORA-12541 are listener and service-name problems, not database-down errors. Check lsnrctl status first.

Further reading

• Oracle Database Concepts
• Oracle Database documentation library
• Oracle AI Database Free

What is New in Oracle’s Eighth Generation SPARC Systems

In this article, we will see what the newly launched Eighth Generation of Oracle SPARC platform has to offer.