Oracle Database = Instance + Database

Instance = SGA (memory) + Background Processes
Database = Physical files on disk

┌─────────────────────────────────────────┐
│              INSTANCE                   │
│  ┌───────────────────────────────────┐  │
│  │         SGA (Shared Memory)       │  │
│  │  ┌──────────┐  ┌──────────────┐  │  │
│  │  │ Shared   │  │ Buffer Cache │  │  │
│  │  │ Pool     │  │ (data blocks)│  │  │
│  │  └──────────┘  └──────────────┘  │  │
│  │  ┌──────────┐  ┌──────────────┐  │  │
│  │  │ Redo Log │  │ Large Pool   │  │  │
│  │  │ Buffer   │  │ (optional)   │  │  │
│  │  └──────────┘  └──────────────┘  │  │
│  └───────────────────────────────────┘  │
│                                         │
│  Background Processes:                  │
│  DBWR - writes dirty blocks to disk     │
│  LGWR - writes redo log entries         │
│  CKPT - signals checkpoints             │
│  SMON - instance recovery on startup    │
│  PMON - cleans up failed processes      │
│  ARCH - archives redo logs              │
├─────────────────────────────────────────┤
│  PGA (Per-Process Memory)               │
│  - Sort area, hash area                 │
│  - Session-specific memory              │
│  - One PGA per server process           │
├─────────────────────────────────────────┤
│              DATABASE (Files)           │
│  Data files (.dbf)                      │
│  Redo log files (.log)                  │
│  Control files (.ctl)                   │
│  Archive logs                           │
│  Parameter file (spfile/pfile)          │
└─────────────────────────────────────────┘

Tablespace = logical storage container in Oracle

Hierarchy:
  Tablespace → Segments → Extents → Data Blocks
  Tablespace → Datafile → OS file on disk

Types of tablespaces:

1. SYSTEM    - data dictionary (DO NOT touch)
2. SYSAUX    - auxiliary system data (AWR, etc.)
3. TEMP      - temporary segments (sorts, joins)
4. UNDO      - undo/rollback data (for transactions)
5. USERS     - default user data tablespace

-- Create a custom tablespace:
CREATE TABLESPACE sales_data
  DATAFILE '/u01/oradata/sales01.dbf'
  SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- Assign to a user:
ALTER USER scott DEFAULT TABLESPACE sales_data;

-- Check tablespace usage:
SELECT tablespace_name,
       ROUND(used_percent, 2) AS pct_used
FROM dba_tablespace_usage_metrics;

Key concept:
  One tablespace can have MULTIPLE datafiles
  One datafile belongs to ONE tablespace
  Tables/indexes are stored IN tablespaces

-- Create user = create schema
CREATE USER hr_app IDENTIFIED BY password123
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

-- hr_app user now has hr_app schema
-- All objects created by hr_app belong to hr_app schema

-- Access another schema's objects:
SELECT * FROM hr_app.employees;
--            ^^^^^^ schema name

-- In PostgreSQL: schema and user are separate
-- In Oracle: schema IS the user
-- This is a common interview trick question

-- Stored procedure: calculate employee bonus
CREATE OR REPLACE PROCEDURE calc_bonus(
    p_emp_id    IN  NUMBER,
    p_dept_id   IN  NUMBER,
    p_bonus     OUT NUMBER
) AS
    v_salary    NUMBER;
    v_rating    VARCHAR2(10);
    v_multiplier NUMBER := 0;
BEGIN
    -- Get employee details
    SELECT salary, performance_rating
    INTO v_salary, v_rating
    FROM employees
    WHERE employee_id = p_emp_id
      AND department_id = p_dept_id;

    -- Calculate multiplier based on rating
    CASE v_rating
        WHEN 'EXCELLENT' THEN v_multiplier := 0.20;
        WHEN 'GOOD'      THEN v_multiplier := 0.15;
        WHEN 'AVERAGE'   THEN v_multiplier := 0.10;
        ELSE v_multiplier := 0.05;
    END CASE;

    p_bonus := v_salary * v_multiplier;

    -- Log the calculation
    INSERT INTO bonus_log(emp_id, bonus_amount, calc_date)
    VALUES (p_emp_id, p_bonus, SYSDATE);

    COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_bonus := 0;
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END calc_bonus;
/

-- Call the procedure:
DECLARE v_bonus NUMBER;
BEGIN
    calc_bonus(101, 10, v_bonus);
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;

Cursors = pointers to result sets in PL/SQL

1. IMPLICIT cursor: automatic for single-row queries
   SELECT salary INTO v_sal FROM employees
   WHERE employee_id = 101;
   -- Oracle creates implicit cursor automatically

2. EXPLICIT cursor: declared for multi-row queries
   Lifecycle: DECLARE → OPEN → FETCH → CLOSE

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;

    v_id    employees.employee_id%TYPE;
    v_name  employees.first_name%TYPE;
    v_sal   employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;

    LOOP
        FETCH emp_cursor INTO v_id, v_name, v_sal;
        EXIT WHEN emp_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(
            v_name || ' earns ' || v_sal);
    END LOOP;

    CLOSE emp_cursor;
END;

-- Cursor attributes:
-- %FOUND    - last fetch returned a row
-- %NOTFOUND - last fetch returned no row
-- %ROWCOUNT - number of rows fetched so far
-- %ISOPEN   - is the cursor open

3. REF CURSOR: dynamic, can change query at runtime
   TYPE ref_cur IS REF CURSOR;
   -- Used for dynamic SQL and returning result sets

Triggers = code that executes automatically on events

Types:
  BEFORE / AFTER    - when it fires
  ROW / STATEMENT   - per row or per statement
  INSERT / UPDATE / DELETE - which DML event

-- Audit trigger: log all salary changes
CREATE OR REPLACE TRIGGER trg_salary_audit
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit_log (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        changed_date
    ) VALUES (
        :OLD.employee_id,
        :OLD.salary,       -- previous value
        :NEW.salary,       -- new value
        USER,
        SYSDATE
    );

    -- Prevent salary decrease > 20%
    IF :NEW.salary < :OLD.salary * 0.8 THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Salary decrease cannot exceed 20%');
    END IF;
END;
/

-- :OLD = values BEFORE the change
-- :NEW = values AFTER the change
-- :OLD is NULL for INSERT
-- :NEW is NULL for DELETE

-- Statement-level trigger (fires once per statement):
CREATE OR REPLACE TRIGGER trg_no_weekend_changes
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
    IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(-20002,
            'No changes allowed on weekends');
    END IF;
END;

-- Generate execution plan:
EXPLAIN PLAN FOR
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;

-- View the plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Sample output:
------------------------------------------------------
| Id | Operation           | Name       | Cost | Rows|
------------------------------------------------------
|  0 | SELECT STATEMENT    |            |   5  |  20 |
|  1 |  HASH JOIN          |            |   5  |  20 |
|  2 |   TABLE ACCESS FULL | DEPARTMENTS|   2  |  10 |
|* 3 |   TABLE ACCESS FULL | EMPLOYEES  |   3  |  20 |
------------------------------------------------------

Key operations to know:
  TABLE ACCESS FULL  → full table scan (bad for large)
  INDEX RANGE SCAN   → using index (good)
  INDEX UNIQUE SCAN  → exact match on unique index
  HASH JOIN          → good for large datasets
  NESTED LOOPS       → good for small datasets
  SORT MERGE JOIN    → both sides sorted

What to look for:
  - Cost: lower is better (relative measure)
  - Cardinality (Rows): estimated row count
  - Full table scans on large tables = problem
  - Missing indexes = add them
  - High cost operations = optimize first

Index = data structure for faster data retrieval

Types:
1. B-Tree Index (default)
   CREATE INDEX idx_emp_salary ON employees(salary);
   -- Best for high-cardinality columns (unique values)

2. Bitmap Index
   CREATE BITMAP INDEX idx_emp_gender
   ON employees(gender);
   -- Best for low-cardinality (M/F, Y/N, status codes)
   -- Great for data warehouses, BAD for OLTP (locking)

3. Function-Based Index
   CREATE INDEX idx_emp_upper_name
   ON employees(UPPER(last_name));
   -- For queries: WHERE UPPER(last_name) = 'KUMAR'

4. Composite Index
   CREATE INDEX idx_emp_dept_sal
   ON employees(department_id, salary);
   -- Order matters! Left-most column used first

When NOT to index:
  ✗ Small tables (full scan is faster)
  ✗ Columns with frequent INSERT/UPDATE/DELETE
    (index maintenance overhead)
  ✗ Low selectivity columns in B-Tree
    (use bitmap instead)
  ✗ Columns rarely used in WHERE/JOIN/ORDER BY

-- Check if index is being used:
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 80000;
-- Look for INDEX RANGE SCAN vs TABLE ACCESS FULL

RMAN = Recovery Manager (Oracle's backup tool)

Backup Types:
1. Full Backup
   RMAN> BACKUP DATABASE;
   -- Backs up all data files

2. Incremental Level 0 (baseline)
   RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
   -- Full backup that serves as incremental base

3. Incremental Level 1 (changes only)
   RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
   -- Only blocks changed since last Level 0/1

4. Archive Log Backup
   RMAN> BACKUP ARCHIVELOG ALL;
   -- Backs up archived redo logs

Common RMAN commands:
  BACKUP DATABASE PLUS ARCHIVELOG;
  BACKUP TABLESPACE users;
  BACKUP AS COMPRESSED BACKUPSET DATABASE;
  LIST BACKUP;
  REPORT NEED BACKUP;

Recovery:
  RMAN> RESTORE DATABASE;    -- copy files from backup
  RMAN> RECOVER DATABASE;    -- apply redo logs
  RMAN> ALTER DATABASE OPEN RESETLOGS;

Flashback Technology (point-in-time recovery):
  FLASHBACK TABLE employees
  TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
  -- Undo changes without full restore

  FLASHBACK DATABASE TO SCN 123456;
  -- Rewind entire database to a point in time

Oracle RAC Architecture:

┌──────────┐  ┌──────────┐  ┌──────────┐
│ Instance │  │ Instance │  │ Instance │
│  Node 1  │  │  Node 2  │  │  Node 3  │
│ SGA+PGA  │  │ SGA+PGA  │  │ SGA+PGA  │
└────┬─────┘  └────┬─────┘  └────┬─────┘
     │             │             │
     └─────────────┼─────────────┘
                   │
          ┌────────┴────────┐
          │  Shared Storage │
          │  (ASM / SAN)    │
          │  One Database   │
          └─────────────────┘

Benefits:
  - High Availability: node fails → others continue
  - Scalability: add nodes for more capacity
  - Load Balancing: distribute connections

Used in India by:
  - Banks (SBI, HDFC) for core banking
  - Telecom (Jio, Airtel) for billing systems
  - Government for 24/7 critical systems

RAC vs Data Guard:
  RAC = HA + scalability (same data center)
  Data Guard = disaster recovery (different location)
  Production setup often uses BOTH together