# Oracle Database: A Practical Guide for Newcomers

A developer joins a bank, opens the main database for the first time, and runs the query that has worked everywhere else in their career:

```sql
SELECT * FROM customers WHERE middle_name = '';
```

Zero rows. Not because every customer has a middle name, but because in Oracle an empty string is `NULL`, and nothing ever equals `NULL`.

Oracle Database is full of moments like this. It is one of the oldest and most capable databases there is, it holds the core records of many banks, airlines, telecom companies and governments, and it does a number of things in its own way. This guide is a map for newcomers: what Oracle is, when it is worth its price, and the rules you need on day one.

## What Oracle Database is

Oracle Database is a commercial relational database, first sold in the late 1970s and developed continuously since. You query it with SQL and write stored logic in **PL/SQL**, its procedural language.

It is proprietary and licensed, usually per processor or per named user, with many advanced features sold as separately priced options. That price is the first thing people mention about Oracle. The second is that it runs enormous, critical workloads very reliably.

You do not have to pay to learn it. **Oracle Database Free** is a no-cost edition with limits on CPU, memory and data size, and it runs in a container:

```bash
docker run -d --name oracle-free -p 1521:1521 \
  -e ORACLE_PWD=Use-A-Strong-One-1 \
  container-registry.oracle.com/database/free:latest
```

### Words you need first

Oracle uses familiar words in unfamiliar ways:

| Term | What it means in Oracle |
|---|---|
| Instance | The running database software: its memory and processes |
| Container database (CDB) | The outer database that holds one or more pluggable databases |
| Pluggable database (PDB) | A self-contained database inside the container. Your application connects to one of these |
| Service name | The name you connect to, which leads to a PDB |
| Schema | A user. Each user owns one schema with the same name, and tables live in it |
| Tablespace | A named area of storage where tables and indexes are kept |

So "create a schema" in Oracle means "create a user". And a connection needs a host, a port (1521 by default) and a service name:

```bash
sqlplus shop_app@//localhost:1521/FREEPDB1
```

`FREEPDB1` is the pluggable database that Oracle Database Free creates for you.

## Why organisations choose it

### Consistent reads without blocking

Oracle was an early user of multi-version read consistency. A query sees the data as it was when the query started, however long it runs, and it never blocks people who are writing. Writers do not block readers either.

### High availability at scale

**Real Application Clusters (RAC)** lets several servers run one database, so a server can fail without an outage. **Data Guard** keeps a standby database in step with the primary for disaster recovery. These are mature, widely used and well understood, though they are paid options.

### Getting data back

**Flashback** features let you look into the past. You can query a table as it was a few minutes ago, as long as the database still holds the old versions:

```sql
SELECT id, status, total
FROM orders AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE id = 42;
```

When someone runs a bad `UPDATE`, this turns a restore-from-backup afternoon into a five-minute fix.

### Depth

Partitioning of very large tables, fine-grained security, auditing, powerful PL/SQL and decades of tuning tools: if a large enterprise needs a database feature, Oracle probably has it.

## Where Oracle fits, and where it does not

**A strong choice for:**

- Core systems at large organisations: banking, billing, reservations, ERP
- Packaged enterprise software that is certified on Oracle
- Very large databases that need clustering and a hot standby
- Companies that already have Oracle licences and skilled administrators

**Look elsewhere when:**

- **You are a startup or a small team.** PostgreSQL covers most of the same ground with no licence cost. Oracle's own free edition is for learning and small workloads.
- **You want to avoid lock-in.** PL/SQL and Oracle-specific SQL are expensive to migrate away from.
- **You have no database administrator.** Oracle rewards expertise and can punish its absence, in both performance and licensing.
- **The job is simple.** An embedded store, a cache or a small website does not need this much machinery.

## Using it the right way

### Learn the dialect's rules

```sql
CREATE TABLE orders (
  id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id NUMBER NOT NULL REFERENCES customers (id),
  total       NUMBER(12,2) NOT NULL CHECK (total >= 0),
  status      VARCHAR2(20) DEFAULT 'pending' NOT NULL,
  created_at  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

-- The newest 20 rows
SELECT id, total FROM orders
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;

-- A calculation with no table uses the one-row table DUAL
SELECT SYSDATE FROM dual;
```

- Use `VARCHAR2` for text and `NUMBER` for numbers.
- An empty string is `NULL`. Test with `IS NULL`, never `= ''`.
- `DATE` holds a date and a time to the second. Forgetting that is a common source of off-by-a-day bugs.
- Names you do not put in double quotes are stored in upper case. `orders` and `ORDERS` are the same table. `"orders"` in quotes is a different one, so avoid quoting names.
- Identity columns and `FETCH FIRST` arrived in version 12c. Older code uses sequences with triggers and `ROWNUM` for the same jobs.

### Commit on purpose

Oracle does not auto-commit your changes in tools such as SQL*Plus. An `UPDATE` is visible only to your session until you run `COMMIT`. Forget, and other sessions wait on your locks; close the window carelessly, and your work may be rolled back.

The opposite surprise: DDL statements such as `CREATE TABLE` and `ALTER TABLE` commit immediately, and they also commit any change you had pending.

### Always use bind variables

Oracle caches the plan for each distinct SQL text. If your application builds a new statement for every value, the cache fills with near-duplicates and the server spends its time parsing.

```sql
-- Wrong: a different statement for every customer
SELECT total FROM orders WHERE customer_id = 42;

-- Right: one statement, reused
SELECT total FROM orders WHERE customer_id = :customer_id;
```

Bind variables also close the door on SQL injection. Every Oracle driver supports them.

### Read the plan

```sql
EXPLAIN PLAN FOR
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```

`TABLE ACCESS FULL` on a big table means every row is read. `INDEX RANGE SCAN` means an index is doing the work. Oracle does not index foreign key columns automatically, so add those indexes yourself:

```sql
CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at);
```

The optimiser depends on statistics. If a plan looks wrong after a large data load, refresh them:

```sql
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
```

### Give each application its own user

```sql
CREATE USER shop_app IDENTIFIED BY "Use-A-Long-Random-One-1"
  QUOTA 500M ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO shop_app;
```

Never let an application connect as `SYS` or `SYSTEM`. For reporting, create a separate user and grant it `SELECT` on only the tables it needs.

### Export with Data Pump, back up with RMAN

Data Pump moves schemas between databases:

```bash
expdp shop_app@//localhost:1521/FREEPDB1 schemas=SHOP_APP \
  directory=DATA_PUMP_DIR dumpfile=shop.dmp logfile=shop_export.log
```

The file is written on the database server, in the folder that the `DATA_PUMP_DIR` directory object points to, not on your own machine. For real backups with point-in-time recovery, Oracle's tool is **RMAN** (Recovery Manager). An export is a useful copy, not a backup strategy.

## Common mistakes

- **Comparing with an empty string.** `= ''` never matches. This also means a `NOT NULL` column rejects empty text.
- **Building SQL by gluing values together.** It hurts performance on Oracle more than on most databases, and it is a security hole.
- **Using features you have not licensed.** Some diagnostic and tuning views belong to paid packs, and partitioning is an extra-cost option. Running them can create a bill at the next audit. Ask your administrator what you are licensed for.
- **Uncommitted work left in a session.** It holds row locks and blocks colleagues.
- **Long reports against busy tables with too little undo.** The result is the well-known `ORA-01555: snapshot too old`. Run the report at a quieter time or have the undo retention raised.
- **Mixing schemas and users in your head.** Granting a privilege to a user affects what they can do; where an object lives depends on which schema owns it.
- **Quoted lower-case names.** A table created as `"orders"` must be quoted forever after.

## Quick reference

| Task | Command |
|---|---|
| Connect | `sqlplus user@//host:1521/service_name` |
| Your tables | `SELECT table_name FROM user_tables;` |
| Describe a table | `DESC orders` |
| Current user | `SELECT USER FROM dual;` |
| Version | `SELECT banner FROM v$version;` |
| Sessions | `SELECT sid, serial#, username, status FROM v$session WHERE type = 'USER';` |
| End a session | `ALTER SYSTEM KILL SESSION 'sid,serial#';` |
| Query plan | `EXPLAIN PLAN FOR ...` then `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);` |
| Refresh statistics | `EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');` |
| Row as it was earlier | `SELECT ... AS OF TIMESTAMP ...` |

Oracle is not hard so much as particular. Learn its vocabulary, commit deliberately, bind your variables and respect the licence, and it will do what it has done for decades: keep very important data safe.

## Exploring Oracle with CreamDB

Oracle's own tools are powerful and heavy. For a quick look at a schema from any machine, [CreamDB](https://www.creamdb.com) connects to Oracle from a browser tab. There is nothing to install, including no Oracle client software.

- Connect with a host, port and service name. Schemas appear in a tree, yours first.
- Open a table in a grid with paging, sorting and filtering done by the database, and export results to CSV, JSON or Excel.
- Write SQL in an editor with autocomplete, formatting and history.
- Read users and roles on a read-only page.
- Ask in plain English and get the SQL, a result table and a chart.

The assistant only reads on its own. Any change waits as an approval card that you answer, and read-only mode is enforced on the server. A database on a private network can be reached through an SSH tunnel.

The live Status panel is not available for Oracle today; it covers PostgreSQL, MySQL, MariaDB, SQL Server and ClickHouse.

It is free to start.

