oracle

Oracle Database

Oracle Database is a multi-model database management system. It is the de-facto standard for Fortune 500 mission-critical systems due to its reliability, scalability (RAC), and PL/SQL power.

When to Use

• Mission-Critical Global Enterprise OLTP: Running Tier-1 core banking, telecommunications, ERP, and supply chain workloads.
• Oracle Real Application Clusters (RAC): Achieving active-active shared-everything database clustering and transparent failover.
• Advanced PL/SQL Procedural Logic: Executing complex enterprise business rules, packages, and triggers directly inside the database.
• Multitenant Pluggable Databases (PDB): Consolidating hundreds of isolated tenant databases under a single Container Database (CDB).

Quick Start

-- Implicit cursor loop in PL/SQL
BEGIN
  FOR r IN (SELECT first_name, last_name FROM employees WHERE department_id = 10)
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.first_name || ' ' || r.last_name);
  END LOOP;
END;
/

Core Concepts

Multitenant Architecture (CDB and PDBs)

One Container Database (CDB) manages system memory and background processes; Pluggable Databases (PDBs) run independently:

[ Container Database (CDB$ROOT) ]
        ├── [ Pluggable DB: PDB_FINANCE ]
        ├── [ Pluggable DB: PDB_HR ]
        └── [ Pluggable DB: PDB_COMMERCE ]

PL/SQL Stored Packages & Transaction Management

Encapsulates procedural business logic with compiled database performance:

CREATE OR REPLACE PACKAGE BODY FinancialOps AS
    PROCEDURE TransferFunds(
        p_from_acc IN NUMBER,
        p_to_acc   IN NUMBER,
        p_amount   IN NUMBER
    ) IS
    BEGIN
        UPDATE Accounts SET Balance = Balance - p_amount WHERE AccountId = p_from_acc;
        UPDATE Accounts SET Balance = Balance + p_amount WHERE AccountId = p_to_acc;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE_APPLICATION_ERROR(-20001, 'Fund transfer failed: ' || SQLERRM);
    END TransferFunds;
END FinancialOps;
/

Automatic Workload Repository (AWR) & ASH

Comprehensive database performance monitoring and execution profiling:

-- Generate AWR Performance Snapshot
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

Common Patterns

Bulk Data Processing with PL/SQL FORALL and BULK COLLECT

Problem: Processing thousands of rows row-by-row in PL/SQL creates massive context-switch overhead between SQL and PL/SQL engines.

Solution: Use BULK COLLECT and FORALL for batch processing:

DECLARE
  TYPE t_emp_list IS TABLE OF employees%ROWTYPE;
  v_emps t_emp_list;
BEGIN
  -- Batch collect into memory
  SELECT * BULK COLLECT INTO v_emps FROM employees WHERE status = 'PENDING';

  -- Batch update in a single engine context switch
  FORALL i IN 1..v_emps.COUNT
    UPDATE employees
    SET status = 'PROCESSED', processed_date = SYSDATE
    WHERE employee_id = v_emps(i).employee_id;

  COMMIT;
END;
/

Best Practices

Do:

• Use Bind Variables Everywhere: Bind variables (:val) prevent hard parsing, reduce latch contention, and stop SQL injection.
• Analyze AWR Reports Regularly: Inspect the "Top 5 Timed Events" in AWR to identify I/O bottlenecks and locking contention.
• Implement Partitioning for Massive Tables: Partition multi-terabyte tables by range or hash to enable partition pruning.
• Use Automatic Memory Management (AMM): Allow Oracle to balance PGA (work areas) and SGA (buffer cache/shared pool) dynamically.

Don't:

• Hardcode literals in production queries: Literal strings force Oracle to recompile and pollute the Shared Pool with distinct execution plans.
• Commit inside iterative row loops: Committing row-by-row causes ORA-01555 Snapshot Too Old errors and redo log thrashing.
• Ignore index monitoring: Drop unused indexes to reclaim storage and reduce write lock overhead.

Troubleshooting

Error	Cause	Solution
ORA-01555: snapshot too old	Rollback segments/undo tablespace overwritten by long-running query.	Increase UNDO_RETENTION and resize undo tablespace.
ORA-00054: resource busy and acquire with NOWAIT specified	Table locked by another active uncommitted DDL or DML transaction.	Wait for transaction to complete or identify blocking session via V$LOCKED_OBJECT.
ORA-01653: unable to extend table ... in tablespace	Tablespace is full or max datafile size reached.	Add a new datafile or enable AUTOEXTEND ON for tablespace.

References

• Oracle Database 23ai Documentation