📖 The Story

You set out to connect Oracle SQL Developer to Oracle Database 21c XE running in Docker on Debian. What looked like a simple “enter username and password” task turned into a multi-layer debugging session spanning IPv6 networking quirks, Oracle’s multitenant architecture, missing grants, and SQL Developer’s credential caching. After methodically isolating each layer — using nc, ss, docker exec, lsnrctl status, and SQL*Plus tests from both the host and inside the container — everything is now working end to end.

The final proof: SELECT * FROM test_table; in SQL Developer returns the exact 1 hello row you created from the CLI moments earlier.

---

🔧 What Was Built

Component	Details
Database	Oracle Database 21c Express Edition (gvenzl/oracle-xe:21-slim Docker image)
Container	oracle-xe, port 1521 published to host
Host OS	Debian (with libaio1 installed)
CLI Client	Oracle Instant Client 21.5 at ~/oracle/instantclient_21_5
GUI Client	Oracle SQL Developer
Admin Account	SYSTEM / NewPassword123 @ 127.0.0.1:1521/XEPDB1
Dev Account	lvydvy / MyDevPassword123 @ 127.0.0.1:1521/XEPDB1

---

🐛 The Three Blockers (and Their Fixes)

1. ORA-01017: invalid username/password

• Cause: The SYSTEM password passed via ORACLE_PWD at container creation was lost/mistyped. Possibly Caps Lock, possibly a Docker env-var typo.
• Fix: Reset inside the container as SYSDBA:bashdocker exec -it oracle-xe sqlplus / as sysdba ALTER USER system IDENTIFIED BY NewPassword123 CONTAINER = ALL;

2. ORA-12170: TNS:Connect timeout occurred

• Cause: localhost on Debian resolves to IPv6 (::1) first. Docker publishes the port on both stacks, but Oracle’s TNS handshake over IPv6 stalls — a well-known Docker + Oracle + Debian gotcha.
• Fix: Use 127.0.0.1 (explicit IPv4) in every connect string — both in SQL Developer and in sqlplus.bashsqlplus system/NewPassword123@127.0.0.1:1521/XEPDB1

3. ORA-01045: user LVYDVY lacks CREATE SESSION privilege

• Cause: The GRANT CONNECT, RESOURCE command was run while connected to the CDB root (XE), not the PDB (XEPDB1) where the user actually lives. Oracle’s multitenant architecture treats them as separate namespaces.
• Fix: Always switch containers before user management:sqlALTER SESSION SET CONTAINER = XEPDB1; GRANT CONNECT, RESOURCE TO lvydvy; GRANT CREATE SESSION TO lvydvy;

Bonus: ORA-01017 in SQL Developer even after CLI worked

• Cause: SQL Developer caches passwords per connection and doesn’t refresh them after a DB-side reset.
• Fix: Clear the Password field completely (Ctrl+A → Delete), retype, then Test.

---

📋 Final Working Configuration

SQL Developer Connection — XE_21c_lvydvy

Field	Value
Connection Name	XE_21c_lvydvy
Username	lvydvy
Password	MyDevPassword123
Role	Default
Hostname	127.0.0.1
Port	1521
Radio button	Service name (not SID)
Service name	XEPDB1

CLI Quick Reference

bash

# Start DB if stopped
docker start oracle-xe

# Admin CLI (from inside container)
docker exec -it oracle-xe sqlplus / as sysdba

# Host-side dev connection
sqlplus lvydvy/MyDevPassword123@127.0.0.1:1521/XEPDB1

# Host-side admin connection
sqlplus system/NewPassword123@127.0.0.1:1521/XEPDB1

Sanity Test Query

sql

SELECT * FROM test_table;
-- ID   NAME
--  1   hello

---

🧠 The Three Rules to Remember

1. Use 127.0.0.1, never localhost — Debian + Docker + Oracle + IPv6 = ORA-12170.
2. Service name XEPDB1, not SID xe — user accounts live in the PDB, not the CDB root.
3. ALTER SESSION SET CONTAINER = XEPDB1; before any CREATE USER, GRANT, or ALTER USER — otherwise you modify the wrong container

🎯 Oracle 21c XE Practice Roadmap

You’ve got a clean working environment. Here’s a structured path from beginner to advanced, all runnable inside SQL Developer as lvydvy. Each tier builds on the previous one.

---

🟢 Tier 1: SQL Fundamentals (1–2 weeks)

Goal: Get fluent with SELECT, filtering, joins, and aggregation.

Practice Ideas

1. Create a small schema — three related tables:
   • customers (id, name, email, city)
   • products (id, name, price, category)
   • orders (id, customer_id, product_id, quantity, order_date)
2. Populate with ~20 rows each using INSERT statements. Use DATE '2026-01-15' literals for dates.
3. Write 30 queries, escalating in difficulty:
   • SELECT * FROM customers WHERE city = 'Tokyo';
   • ORDER BY price DESC, LIMIT equivalents (FETCH FIRST 5 ROWS ONLY)
   • INNER JOIN, LEFT JOIN, RIGHT JOIN across all three tables
   • GROUP BY with COUNT, SUM, AVG, MIN, MAX
   • HAVING clauses
   • Subqueries (in WHERE, in FROM, correlated)
   • CASE WHEN for conditional columns
4. Window functions — ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), running totals with SUM() OVER (ORDER BY ...)

Oracle-Specific Touches

• Use DUAL for one-row queries: SELECT SYSDATE FROM DUAL;
• Try NVL(), NVL2(), COALESCE(), DECODE()
• Practice TO_CHAR, TO_DATE, TO_NUMBER conversions

---

🟡 Tier 2: Schema Objects & DDL (1 week)

Goal: Move beyond querying — design and manage structures.

Practice Ideas

1. Constraints — add PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL to your tables
2. Indexes — create B-tree indexes on foreign key columns, then check EXPLAIN PLAN for a join query with and without them
3. Views — create a v_customer_orders view that joins all three tables; query it like a table
4. Sequences — use CREATE SEQUENCE seq_order_id START WITH 1000; and reference it in inserts
5. Identity columns — 21c supports GENERATED BY DEFAULT AS IDENTITY
6. Materialized views — CREATE MATERIALIZED VIEW mv_sales_summary AS SELECT ... and refresh it
7. Synonyms — CREATE SYNONYM cust FOR customers;
8. Temporary tables — CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS;

Practice Task

Drop and recreate your entire schema using a single SQL script. Save it as setup_schema.sql and run it with F5 (Run Script) in SQL Developer.

---

🟠 Tier 3: PL/SQL Programming (2–3 weeks)

Goal: Learn Oracle’s procedural language — the biggest differentiator for Oracle developers.

Practice Ideas

1. Anonymous blocks — DECLARE ... BEGIN ... END;
2. Stored procedures — CREATE OR REPLACE PROCEDURE add_customer(...) IS ...
3. Functions — return values, use them in SELECT
4. Packages — group related procedures/functions; learn the spec/body split
5. Cursors — explicit cursors with OPEN, FETCH, CLOSE; then FOR ... IN cursor LOOP
6. Exceptions — WHEN NO_DATA_FOUND, WHEN OTHERS, custom exceptions with RAISE_APPLICATION_ERROR
7. Triggers — BEFORE INSERT, AFTER UPDATE on your tables
8. Dynamic SQL — EXECUTE IMMEDIATE
9. Collections — VARRAY, nested tables, associative arrays (INDEX BY PLS_INTEGER)

Practice Project

Build an order processing package:

• add_order(customer_id, product_id, quantity) — inserts with validation
• get_customer_total(customer_id) — returns total spend
• A trigger that decrements a stock column when an order is inserted
• Exception handling for out-of-stock and invalid-customer cases

---

🔴 Tier 4: Performance & Administration (ongoing)

Goal: Think like a DBA.

Practice Ideas

1. EXPLAIN PLAN FOR — inspect execution plans, look for TABLE ACCESS FULL vs INDEX RANGE SCAN
2. DBMS_XPLAN.DISPLAY — format plans nicely
3. Statistics — DBMS_STATS.GATHER_TABLE_STATS
4. Hints — /*+ INDEX(...) */, /*+ PARALLEL(4) */
5. AWR/ASH reports — even XE has some, though limited
6. V$ views — V$SESSION, V$SQL, V$LOCK, V$PARAMETER
7. Backup/restore — expdp/impdp from the container:bashdocker exec -it oracle-xe expdp system/NewPassword123@XEPDB1 \ directory=DATA_PUMP_DIR dumpfile=lvydvy.dmp schemas=lvydvy
8. Tablespace management — create a new tablespace, move a table into it, check DBA_TABLESPACES

---

🧪 Tier 5: Real-World Scenario Projects

Pick one and build it end to end.

Project A: Library Management System

• Tables: books, members, loans, fines
• Business rules: a member can borrow max 3 books; overdue fines accrue daily
• PL/SQL package that enforces rules on loan and return
• Views for “overdue loans”, “most borrowed books”

Project B: E-commerce Backend

• Tables: users, products, inventory, orders, order_items, payments
• Procedure place_order that checks stock, computes total, inserts order + items in one transaction
• Trigger that logs all price changes to an audit_prices table
• Materialized view for daily sales

Project C: HR Analytics

• Load Oracle’s sample HR schema (available in XE) or build your own
• Write analytic queries: top earners per department, salary percentile, running headcount
• Create a dashboard-style view with window functions

---

📚 Free Learning Resources

Resource	Why
Oracle Live SQL (livesql.oracle.com)	Run snippets without installing anything
Oracle Database 21c Docs	The official reference — bookmark the SQL Reference and PL/SQL Language Reference
Ask TOM (asktom.oracle.com)	Tom Kyte’s Q&A archive — the gold standard for Oracle questions
Oracle Dev Gym (devgym.oracle.com)	Free quizzes and workouts on SQL/PLSQL
Stack Overflow oracle tag	Real-world problems and solutions

---

🗓️ Suggested 8-Week Schedule

Week	Focus
1	Tier 1 — basic SELECT, WHERE, ORDER BY, single-table
2	Tier 1 — joins, GROUP BY, subqueries
3	Tier 2 — DDL, constraints, indexes, views
4	Tier 2 + first Tier 5 project scaffold
5	Tier 3 — PL/SQL basics, procedures, functions
6	Tier 3 — packages, triggers, cursors
7	Tier 4 — EXPLAIN PLAN, V$ views, performance tuning
8	Tier 5 — complete one full project end to end

---

🎯 My Recommendation

Start with Tier 1 + Project B (E-commerce). It’s the most transferable to real jobs, gives you a reason to learn every subsequent tier (you’ll want triggers and packages to enforce business rules), and lets you practice joins, aggregation, and PL/SQL on data that actually makes sense.