✅ Complete Oracle Database Setup & User Guide

🧱 PART 1: Install Oracle Database (Windows)

🧰 Prerequisites:

• OS: Windows 10/11 (64-bit)

• Oracle 19c installer: Download from Oracle

• Java JDK (for SQL Developer/JDBC)

---

📥 1. Installation Steps:

1. Run installer and choose:

   Create and configure a single instance database
   

2. System Class: Choose Desktop (or Server if applicable)

3. Oracle Base: Keep default (e.g. C:\app\oracle\)

4. Oracle Home Name & Path: Accept defaults

5. Database Edition: Enterprise or Standard

6. Database Configuration:

   • Global Database Name: ORCLCDB

   • SID: ORCLCDB

   • Enable container database (for CDB/PDB setup)

   • Create a pluggable database (e.g. ORCLPDB)

   • Set common password for SYS/SYSTEM

7. Summary > Install

---

🔧 Post-Install Configuration:

• Services:

  • OracleServiceORCLCDB

  • OracleOraDB19Home1TNSListener

• Verify Listener is running: lsnrctl status

---

📁 PART 2: Oracle Directory Structure

Term	Description
ORACLE_HOME	Root Oracle install directory
ORACLE_BASE	Base path for all Oracle files
tnsnames.ora	Connection configuration for clients
listener.ora	Listener configuration for remote connections

To find ORACLE_HOME, run:

reg query HKLM\SOFTWARE\ORACLE

---

🧠 PART 3: Understanding CDB & PDB

Term	Meaning
CDB	Container Database – system-wide container
PDB	Pluggable Database – individual app database
CDB$ROOT	Root container holding Oracle metadata
PDB$SEED	Template to create new PDBs
ORCLPDB	Default user database

📌 Best practice: create schemas in PDB, not CDB.

---

🔐 PART 4: Creating Users & Schemas

A. Connect to PDB (Recommended):

ALTER SESSION SET CONTAINER = ORCLPDB;

-- Create user/schema
CREATE USER myapp IDENTIFIED BY "MyPass@123";
GRANT CONNECT, RESOURCE TO myapp;
ALTER USER myapp QUOTA UNLIMITED ON users;

B. Connect to CDB (if using SID):

ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE USER C##MYAPP IDENTIFIED BY "MyPass@123";
GRANT CONNECT, RESOURCE TO C##MYAPP;
ALTER USER C##MYAPP QUOTA UNLIMITED ON users;

---

🧩 PART 5: SQL Developer Setup

1. Open SQL Developer

2. Add New Connection:

   • Username: myapp

   • Password: MyPass@123

   • Connection Type: Basic

   • Hostname: localhost

   • Port: 1521

   • Service Name: ORCLPDB (or SID: ORCLCDB)

Click Test, then Connect

---

🧬 PART 6: JDBC Connection String

A. Using Service Name (Recommended for PDB)

jdbc:oracle:thin:@//localhost:1521/ORCLPDB

B. Using SID

jdbc:oracle:thin:@localhost:1521:ORCLCDB

Use this if you're using a CDB-level common user like C##MYAPP.

---

🏗️ PART 7: Running Schema DDL/DML

Example script:

CREATE TABLE MYAPP.ITEMS (
  ID CHAR(36) PRIMARY KEY,
  NAME VARCHAR2(100)
);

INSERT INTO MYAPP.ITEMS VALUES ('UUID-001', 'Example');

SELECT * FROM MYAPP.ITEMS;

---

🛠️ PART 8: Common Errors & Fixes

Error	Cause	Fix
ORA-65096: invalid common user	Missing C##	Use C##USERNAME in CDB
ORA-01950: no privileges on tablespace	No quota	ALTER USER myapp QUOTA UNLIMITED ON users;
ORA-01109: database not open	DB not open	ALTER DATABASE OPEN; as SYSDBA
ORA-00942: table or view does not exist	Wrong user or container	Check CONTAINER and user/schema context

---

🚪 PART 9: Switching Between CDB and PDB

-- To root
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- To specific PDB
ALTER SESSION SET CONTAINER = ORCLPDB;

---

🧹 PART 10: Maintenance & Cleanup

-- Drop PDB user
ALTER SESSION SET CONTAINER = ORCLPDB;
DROP USER myapp CASCADE;

-- Drop CDB user
ALTER SESSION SET CONTAINER = CDB$ROOT;
DROP USER C##MYAPP CASCADE;

---