Oracle Database Tutorial

Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation. It is one of the most widely used databases for large business systems, including banking, telecommunications, government, retail and enterprise resource planning (ERP) applications, where reliability, security and performance at scale are essential.

This tutorial explains Oracle Database from the ground up: its architecture, the multitenant model, schemas and data types, SQL with Oracle-specific features, transactions and read consistency, PL/SQL procedures, functions, packages and triggers, indexes and query tuning, backup and high availability, and connecting from Java. Every section includes small examples you can run on the free edition.

1. What Is Oracle Database?

Oracle Database stores data in tables of rows and columns, enforces relationships with keys and constraints, and lets you query and change data with SQL. It extends SQL with PL/SQL, a procedural language for writing stored procedures, functions, packages and triggers that run inside the database.

Today it is a multi-model database: besides relational tables, the same database can store and query JSON documents, XML, spatial and graph data, and, in recent versions, vector embeddings for AI similarity search.

Organisations choose Oracle for:

• Reliability: mature crash recovery, full ACID transactions and decades of production use in critical systems.
• Scalability: features such as Real Application Clusters (RAC) let several servers run one database.
• Availability: Data Guard standby databases, online maintenance and Flashback technologies minimise downtime and data loss.
• Security: fine-grained access control, encryption, auditing and data redaction.
• Rich tooling: an advanced cost-based optimiser, diagnostic tools and a large ecosystem of enterprise applications.

2. A Short History of Oracle Database

Oracle Corporation was founded in 1977 by Larry Ellison, Bob Miner and Ed Oates, under the name Software Development Laboratories. Inspired by IBM's research on relational databases and SQL, they built one of the first commercially available SQL relational database systems.

Since 2018, Oracle releases have been numbered after years. Releases are either long-term (such as 19c and the 23ai/26ai line), recommended for production with years of support, or innovation releases (such as 21c) with a shorter support window.

3. Key Features and Editions

Oracle Database is commercial software. The main editions are:

• Enterprise Edition: all core features, with many advanced capabilities sold as separately licensed options.
• Standard Edition 2: a lower-cost edition for smaller servers, with a reduced feature set.
• Oracle Database Free: a free edition for learning, development and small applications, with limits on CPU, memory and data size. It replaced the older Express Edition (XE).
• Autonomous Database: a managed cloud service on Oracle Cloud Infrastructure that automates patching, tuning and backups.

4. Oracle Architecture: Instance and Database

Oracle distinguishes between the instance and the database:

• The instance is the set of memory structures and background processes running on a server.
• The database is the set of physical files on disk that hold the data.

4.1 Memory structures

• System Global Area (SGA): shared memory used by all sessions. It contains the database buffer cache (copies of data blocks), the shared pool (parsed SQL, execution plans and the data dictionary cache) and the redo log buffer (records of changes waiting to be written).
• Program Global Area (PGA): private memory for each server process, used for sorting, hashing and session information.

4.2 Background processes

• DBWn (database writer): writes changed blocks from the buffer cache to the data files.
• LGWR (log writer): writes redo records to the online redo log files. A COMMIT completes once LGWR has written the transaction's redo, which guarantees durability.
• CKPT (checkpoint): records checkpoint information in the control files and data file headers.
• SMON (system monitor) performs recovery when the instance starts after a crash, and PMON (process monitor) cleans up after failed sessions.
• ARCn (archiver): copies filled redo log files to archive storage when the database runs in ARCHIVELOG mode, enabling point-in-time recovery.

4.3 Database files

• Data files hold tables, indexes and other data.
• Control files record the database's structure, such as the names and locations of its files.
• Online redo log files record every change, so committed transactions can be recovered after a failure.

5. The Multitenant Architecture

Since 12c, and as the only supported architecture from 21c onwards, an Oracle database is a container database (CDB) that holds one or more pluggable databases (PDBs). Each PDB looks to applications like an independent database with its own users, schemas and data, while sharing the instance's memory and background processes.

Benefits include consolidating many databases onto one server, upgrading or patching several databases at once, and quickly cloning, unplugging and plugging PDBs to move them between servers. In Oracle Database Free, the default PDB is named FREEPDB1.

6. Running Oracle Database Free with Docker

The easiest way to practise is to run Oracle Database Free in a container using the official image from Oracle's container registry:

# Start Oracle Database Free (the first start takes a few minutes)
docker run -d --name oracle-free -p 1521:1521 \
  -e ORACLE_PWD=ChangeMe_123 \
  container-registry.oracle.com/database/free:latest

# Follow the log until it reports that the database is ready to use
docker logs -f oracle-free

# Connect with SQL*Plus inside the container to the pluggable database
docker exec -it oracle-free sqlplus system/ChangeMe_123@//localhost:1521/FREEPDB1

Useful client tools include SQL Developer (a free graphical IDE), SQLcl (a modern command-line client), the classic SQL*Plus, and the Oracle extension for Visual Studio Code. Alternatively, Oracle offers free cloud databases for learning through its Always Free cloud tier.

7. Users, Schemas and Tablespaces

In Oracle, a user and a schema are closely linked: when you create a user, Oracle creates a schema of the same name to own that user's tables, views and other objects. Data is stored in tablespaces, logical storage containers made up of one or more data files.

-- Run as SYSTEM while connected to FREEPDB1
CREATE USER shop IDENTIFIED BY "Shop_Pass_2026"
  DEFAULT TABLESPACE users
  QUOTA 200M ON users;

-- DB_DEVELOPER_ROLE (23ai and later) bundles the usual development privileges
GRANT db_developer_role TO shop;
-- On older versions: GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW,
--                    CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER TO shop;

-- Then connect as the new user
-- sqlplus shop/Shop_Pass_2026@//localhost:1521/FREEPDB1

Storage is organised in a hierarchy: a tablespace contains segments (one per table or index), segments are made of extents, and extents are made of data blocks, the smallest unit of I/O (8 KB by default).

8. Oracle Data Types

9. SQL Basics in Oracle

9.1 Create tables with constraints

CREATE TABLE departments (
  dept_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  dept_name  VARCHAR2(60) NOT NULL UNIQUE,
  location   VARCHAR2(60)
);

CREATE TABLE employees (
  emp_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name  VARCHAR2(100) NOT NULL,
  email      VARCHAR2(120) NOT NULL UNIQUE,
  salary     NUMBER(10, 2) CHECK (salary > 0),
  hire_date  DATE DEFAULT SYSDATE NOT NULL,
  dept_id    NUMBER REFERENCES departments (dept_id),
  manager_id NUMBER REFERENCES employees (emp_id)
);

9.2 Insert, query, update and delete

INSERT INTO departments (dept_name, location) VALUES ('Engineering', 'Bengaluru');
INSERT INTO departments (dept_name, location) VALUES ('Sales', 'Mumbai');

INSERT INTO employees (full_name, email, salary, dept_id)
VALUES ('Anita Rao', 'anita@example.com', 145000, 1);
INSERT INTO employees (full_name, email, salary, dept_id, manager_id)
VALUES ('Vikram Shah', 'vikram@example.com', 98000, 1, 1);
INSERT INTO employees (full_name, email, salary, dept_id)
VALUES ('Leena Das', 'leena@example.com', 87000, 2);
COMMIT;

-- Join and aggregate
SELECT d.dept_name, COUNT(*) AS staff, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;

-- Top 2 earners (12c and later)
SELECT full_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 2 ROWS ONLY;

UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 2;
DELETE FROM employees WHERE email = 'leena@example.com';
COMMIT;

Unlike many other databases' clients, Oracle tools do not commit automatically by default: changes are only permanent after COMMIT. Older Oracle versions also require FROM dual for queries that do not read a table, such as SELECT SYSDATE FROM dual;; from 23ai, SELECT SYSDATE; works too.

10. Oracle-Specific SQL Features

10.1 Sequences, MERGE and NULL functions

-- Sequence: an independent number generator
CREATE SEQUENCE invoice_seq START WITH 1000 INCREMENT BY 1;
SELECT invoice_seq.NEXTVAL FROM dual;

-- MERGE: insert or update in one statement (an "upsert")
MERGE INTO departments d
USING (SELECT 'Sales' AS dept_name, 'Pune' AS location FROM dual) src
ON (d.dept_name = src.dept_name)
WHEN MATCHED THEN UPDATE SET d.location = src.location
WHEN NOT MATCHED THEN INSERT (dept_name, location) VALUES (src.dept_name, src.location);

-- Handling NULLs and conditions
SELECT full_name,
       NVL(manager_id, 0)                        AS manager_or_zero,
       COALESCE(TO_CHAR(manager_id), 'No manager') AS manager_label,
       CASE WHEN salary >= 120000 THEN 'Senior'
            WHEN salary >= 90000  THEN 'Mid'
            ELSE 'Junior' END                    AS band
FROM employees;

-- Dates
SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI') AS now_text,
       ADD_MONTHS(SYSDATE, 3)                  AS in_three_months,
       TRUNC(SYSDATE, 'MM')                    AS first_of_month
FROM dual;

10.2 Analytic functions and hierarchical queries

-- Rank employees within each department without collapsing rows
SELECT full_name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)  AS dept_rank,
       SUM(salary) OVER (PARTITION BY dept_id)                   AS dept_total
FROM employees;

-- Walk the reporting hierarchy from the top manager downwards
SELECT LPAD(' ', 2 * (LEVEL - 1)) || full_name AS org_chart, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

Oracle also supports the SQL standard recursive WITH clause, JSON querying with functions such as JSON_VALUE and JSON_TABLE, and, from 23ai, conveniences like CREATE TABLE IF NOT EXISTS and GROUP BY with column aliases.

11. Transactions and Read Consistency

A transaction starts automatically with the first data change and ends with COMMIT or ROLLBACK. SAVEPOINT lets you undo part of a transaction.

UPDATE employees SET salary = salary + 5000 WHERE emp_id = 2;
SAVEPOINT after_raise;

UPDATE employees SET dept_id = 2 WHERE emp_id = 2;
ROLLBACK TO after_raise;     -- undo only the department change

COMMIT;                      -- the raise becomes permanent

-- Lock rows you are about to change; NOWAIT fails instead of waiting
SELECT salary FROM employees WHERE emp_id = 2 FOR UPDATE NOWAIT;

Oracle provides read consistency using undo data. When a row is changed, the old version is kept in an undo tablespace. Queries that started earlier use the undo to rebuild the data as it was when they began, so readers never block writers and writers never block readers. Undo is also what makes ROLLBACK and Flashback Query possible.

• The default isolation level is Read Committed: each statement sees data committed before it started.
• Serializable isolation makes a whole transaction see one snapshot and raises an error if it tries to change data modified by another transaction since it began.
• Every DDL statement, such as CREATE or ALTER, commits implicitly, including any pending changes made before it.

12. PL/SQL Basics

PL/SQL (Procedural Language/SQL) adds variables, conditions, loops, error handling and modular code to SQL. Code is organised in blocks with optional DECLARE, mandatory BEGIN ... END and an optional EXCEPTION section.

SET SERVEROUTPUT ON

DECLARE
  v_name     employees.full_name%TYPE;     -- same type as the column
  v_salary   employees.salary%TYPE;
  v_bonus    NUMBER(10, 2);
BEGIN
  SELECT full_name, salary INTO v_name, v_salary
  FROM employees
  WHERE emp_id = 1;

  IF v_salary > 120000 THEN
    v_bonus := v_salary * 0.10;
  ELSE
    v_bonus := v_salary * 0.05;
  END IF;

  DBMS_OUTPUT.PUT_LINE(v_name || ' gets a bonus of ' || v_bonus);

  -- Cursor FOR loop: iterate over query results
  FOR r IN (SELECT full_name, salary FROM employees ORDER BY salary DESC) LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(r.full_name, 15) || r.salary);
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

%TYPE and %ROWTYPE declare variables with the same type as a column or row, so code keeps working if the table definition changes. The slash on its own line tells SQL*Plus and SQLcl to run the block.

13. Procedures, Functions, Packages and Triggers

-- Package specification: the public interface
CREATE OR REPLACE PACKAGE hr_pkg AS
  FUNCTION annual_cost(p_dept_id IN NUMBER) RETURN NUMBER;
  PROCEDURE give_raise(p_emp_id IN NUMBER, p_percent IN NUMBER);
END hr_pkg;
/

-- Package body: the implementation
CREATE OR REPLACE PACKAGE BODY hr_pkg AS

  FUNCTION annual_cost(p_dept_id IN NUMBER) RETURN NUMBER IS
    v_total NUMBER;
  BEGIN
    SELECT NVL(SUM(salary), 0) INTO v_total
    FROM employees WHERE dept_id = p_dept_id;
    RETURN v_total;
  END annual_cost;

  PROCEDURE give_raise(p_emp_id IN NUMBER, p_percent IN NUMBER) IS
  BEGIN
    IF p_percent NOT BETWEEN 0 AND 30 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Raise must be between 0 and 30 percent');
    END IF;

    UPDATE employees
    SET salary = salary * (1 + p_percent / 100)
    WHERE emp_id = p_emp_id;

    IF SQL%ROWCOUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Employee ' || p_emp_id || ' not found');
    END IF;
  END give_raise;

END hr_pkg;
/

-- Use them
BEGIN
  hr_pkg.give_raise(p_emp_id => 2, p_percent => 8);
  COMMIT;
END;
/
SELECT dept_name, hr_pkg.annual_cost(dept_id) AS cost FROM departments;

Packages group related procedures, functions, types and variables. They separate the public interface from the implementation, load into memory as a unit and are the standard way to organise PL/SQL. A trigger runs automatically when data changes:

CREATE TABLE salary_audit (
  emp_id      NUMBER,
  old_salary  NUMBER(10, 2),
  new_salary  NUMBER(10, 2),
  changed_by  VARCHAR2(128),
  changed_at  TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)
BEGIN
  INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_by)
  VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, USER);
END;
/

For processing many rows, use bulk operations: BULK COLLECT fetches many rows into a collection at once, and FORALL sends many DML statements in one round trip. They are far faster than row-by-row loops.

14. Indexes and Performance Tuning

-- Index foreign keys used in joins (Oracle does not create these automatically)
CREATE INDEX emp_dept_idx ON employees (dept_id);

-- Function-based index for case-insensitive searches
CREATE INDEX emp_email_upper_idx ON employees (UPPER(email));

-- Show the execution plan the optimiser chooses
EXPLAIN PLAN FOR
  SELECT full_name FROM employees WHERE UPPER(email) = 'ANITA@EXAMPLE.COM';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Keep optimiser statistics up to date (normally done by an automatic job)
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES');

Two habits matter more than any parameter tweak: use bind variables instead of literal values in application SQL, so Oracle can reuse parsed statements from the shared pool, and keep statistics current, so the optimiser can choose good plans. Enterprise tools such as the Automatic Workload Repository (AWR) reports and SQL Tuning Advisor help diagnose problems in larger systems; check your licence, as some of these are separately licensed packs.

15. Backup, Recovery and High Availability

• RMAN (Recovery Manager): Oracle's built-in tool for physical backups, incremental backups, restore and recovery, including point-in-time recovery when the database runs in ARCHIVELOG mode.
• Data Pump (expdp/impdp): logical export and import of schemas or tables, often used for migrations and copying data between environments.
• Flashback: view data as it was in the past (AS OF TIMESTAMP), recover dropped tables from the recycle bin, or rewind a whole database.
• Data Guard: maintains one or more standby databases that can take over if the primary fails; Active Data Guard also allows read-only queries on the standby.
• Real Application Clusters (RAC): multiple servers run one database, so the loss of a server does not stop the application.

-- See what the table looked like 10 minutes ago
SELECT emp_id, salary
FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

-- Recover a table dropped by mistake from the recycle bin
DROP TABLE salary_audit;
FLASHBACK TABLE salary_audit TO BEFORE DROP;

16. Connecting to Oracle from Java

Java applications connect with the Oracle JDBC driver. Add it with Maven, choosing the artifact that matches your Java version (ojdbc11 for Java 11 and later; newer releases also offer ojdbc17), and check Maven Central for the latest version:

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc11</artifactId>
  <version>23.5.0.24.07</version>
</dependency>

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class EmployeeDao {

    // Thin driver URL using the PDB service name
    private static final String URL = "jdbc:oracle:thin:@//localhost:1521/FREEPDB1";

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "shop", "Shop_Pass_2026")) {
            conn.setAutoCommit(false);

            // Bind variables (?) prevent SQL injection and let Oracle reuse the parsed statement
            String sql = "SELECT full_name, salary FROM employees WHERE dept_id = ? ORDER BY salary DESC";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setInt(1, 1);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        BigDecimal salary = rs.getBigDecimal("salary");
                        System.out.printf("%-15s %s%n", rs.getString("full_name"), salary);
                    }
                }
            }

            // Call a PL/SQL procedure and a function from the hr_pkg package
            try (CallableStatement cs = conn.prepareCall("{call hr_pkg.give_raise(?, ?)}")) {
                cs.setInt(1, 2);
                cs.setBigDecimal(2, new BigDecimal("3"));
                cs.execute();
            }
            try (CallableStatement fn = conn.prepareCall("{? = call hr_pkg.annual_cost(?)}")) {
                fn.registerOutParameter(1, Types.NUMERIC);
                fn.setInt(2, 1);
                fn.execute();
                System.out.println("Engineering annual cost: " + fn.getBigDecimal(1));
            }
            conn.commit();
        }
    }
}

In Spring Boot, set spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/FREEPDB1 with a username and password; Spring Boot then uses the HikariCP connection pool, and Spring Data JPA with Hibernate supports Oracle out of the box. Oracle also provides its own Universal Connection Pool (UCP) with features for RAC and Data Guard.

17. Oracle vs. MySQL vs. PostgreSQL

To compare in more depth, see the MySQL vs. Oracle article, the MySQL tutorial and the PostgreSQL tutorial.

18. Best Practices and Common Mistakes

Best practices

• Use bind variables in every application query.
• Define constraints (primary keys, foreign keys, NOT NULL, CHECK) so the database guarantees data quality.
• Index foreign keys and columns used in frequent filters and joins, and verify plans with DBMS_XPLAN.
• Organise PL/SQL in packages and process data in sets or bulk operations, not row by row.
• Keep transactions short and commit at the end of a logical unit of work, not after every row.
• Grant least privilege: give application users only the access they need, and never use SYS or SYSTEM for applications.
• Run in ARCHIVELOG mode with tested RMAN backups for production databases.
• Understand your licence before enabling options and management packs.

Common mistakes to avoid

19. Summary

• Oracle Database is a commercial, multi-model relational database built for reliability, security and scale.
• An instance (SGA, PGA and background processes) operates on a database (data files, control files and redo logs).
• Modern Oracle uses a multitenant architecture: one container database holds pluggable databases.
• Users own schemas, and data is stored in tablespaces made of segments, extents and blocks.
• Oracle SQL adds sequences, MERGE, analytic functions, hierarchical queries and powerful date functions; empty strings are NULL.
• Undo-based read consistency means readers and writers do not block each other; DDL commits implicitly.
• PL/SQL provides blocks, procedures, functions, packages, triggers and bulk processing.
• RMAN, Data Pump, Flashback, Data Guard and RAC cover backup, recovery and high availability; Java connects through the JDBC thin driver.

A good next step is to run Oracle Database Free in Docker, create the employees schema above, write a package that manages salaries, and call it from a small Java or Spring Boot application. Then open the FAQ tab for answers to the questions learners most often ask about Oracle.

Frequently Asked Questions (FAQ)