Oracle Tutorials

Oracle Database from the ground up — architecture, SQL, PL/SQL, indexes, transactions and Spring Boot integration.

• Oracle Database – With Spring Boot
  Driver, URL, and the colon-versus-slash that decides between a SID and a service name. A tuned Hikari and JPA configuration, a Lombok entity that does not break hashCode, the allocationSize mismatch that causes ORA-00001 under load, batching, calling PL/SQL packages, Testcontainers against real Oracle, and a table of the errors you will hit.
• Oracle Database – Transactions and Locking
  Why readers never block writers, and how undo delivers both rollback and read consistency. Where a transaction starts, the DDL that commits behind your back, the two isolation levels Oracle actually has and the ORA-08177 retry that SERIALIZABLE requires. SELECT FOR UPDATE SKIP LOCKED as a work queue, deadlocks, and finding the blocker.
• Oracle Database – Indexes and Execution Plans
  Tuning Oracle is one skill: get the real plan and compare estimated rows against actual rows. Index types and the composite leading-column rule, the four reasons your index is ignored — including implicit conversion and the NULLs a B-tree does not store — DBMS_XPLAN with GATHER_PLAN_STATISTICS, bind variables, and invisible indexes.
• Oracle Database – PL/SQL
  Anonymous blocks, %TYPE and %ROWTYPE, the two exceptions SELECT INTO can raise, cursor FOR loops, and packages as the unit of organisation. Exception handling done properly, why WHEN OTHERS THEN NULL is the worst line of code in the Oracle world, BULK COLLECT and FORALL with SAVE EXCEPTIONS — and when to write plain SQL instead.
• Oracle Database – Analytic Functions
  Window functions are the feature that most changes how you write SQL. ROW_NUMBER, RANK and DENSE_RANK compared on ties, top-N per group, deduplication by ROWID, LAG and LEAD, running totals — including the ROWS versus RANGE default that quietly gives wrong numbers — the LAST_VALUE frame trap, KEEP DENSE_RANK, and gaps and islands.
• Oracle Database – CROSS APPLY and OUTER APPLY
  An inline view that can see the row it is joined to, evaluated once per outer row. The four spellings and why CROSS APPLY silently drops rows where OUTER APPLY keeps them. Top-N per group and exactly when it beats ROW_NUMBER, several aggregates in one pass, per-row table functions, and how to read Starts and WINDOW NOSORT STOPKEY in the plan.
• Oracle Database – Joins
  ANSI joins, the legacy (+) operator you will meet in every pre-2005 codebase and why missing one silently turns an outer join into an inner one, semi-joins with EXISTS, and the NOT IN trap that returns zero rows without an error when the subquery contains a NULL. Plus the correlated join a plain JOIN cannot do, the three join methods Oracle picks between, and how to recognise a bad nested loop in a plan.
• Oracle Database – SELECT Essentials
  The querying idioms that are specific to Oracle: DUAL, FETCH FIRST versus the ROWNUM pagination trap, NVL against COALESCE, NULLS FIRST, date format masks where MM and MI are not the same thing, LISTAGG and its 4000-byte overflow, ROLLUP, MERGE as the upsert, and INSERT ALL because Oracle rejects a multi-row VALUES list.
• Oracle Database – Tables, Constraints and Sequences
  A CREATE TABLE worth copying, and the five constraint types. Why an unindexed foreign key locks the child table, why composite UNIQUE lets partly-null duplicates through, and the ON UPDATE CASCADE Oracle does not have. Identity columns versus sequences, CACHE and the gaps it creates, and how to drop a column on a large table.
• Oracle Database – Data Types
  VARCHAR2 versus CHAR and why the BYTE/CHAR length qualifier matters in UTF-8. NUMBER precision and why money never goes in a binary float. The fact that DATE includes a time, and the range comparison that follows from it. Booleans before 23ai, the empty string that is NULL, and a full mapping table to Java types.
• Oracle Database – Users, Schemas and Privileges
  In Oracle a user is a schema, so CREATE USER is how you create a namespace. Setting up a proper application account: why a tablespace quota is not optional, what CONNECT and RESOURCE really grant, the owner/role/service-account split, three ways to stop qualifying table names, and the password expiry that locks out service accounts.
• Oracle Database – Run It Locally with Docker
  Oracle Database Free in a container: the image to pull, the three environment variables that matter, how to tell when the database is genuinely ready, and why you must connect to the FREEPDB1 service rather than FREE. Includes a docker-compose file with schema init scripts, and the gotchas on Apple Silicon.
• Oracle Database – Introduction
  Oracle Database is what you meet the moment you work on anything old and important. Instance versus database, the tablespace-to-block storage hierarchy, CDB and PDB, which edition to install, and a table of every place Oracle behaves differently from MySQL and Postgres — a user is a schema, an empty string is NULL, and a DATE carries a time.