🎯

oracle

🎯Skill
g1joshi/agent-skills1 Installs9Last Updated Feb 10, 2026

Installation

$npx skills add g1joshi/agent-skills --skill oracle

Run it in a terminal; it asks which AI coding tools to install it for (Claude Code, Codex, Cursor and more).

Uses skills.sh Β· Node.js 22.20+

After installing

Ask for related work and the agent uses it on its own, or call it yourself.

Run it (Claude Code)/oracle
Install by hand

Copy the whole skill folder from GitHub to the place below. Copying only SKILL.md can leave out files the skill uses.

Open the skill folder β†’
  • This project .claude/skills/oracle/
  • All projects ~/.claude/skills/oracle/

These paths are for Claude Code. For other tools, the command above puts it in the right place.

Expert Oracle Database assistance covering PL/SQL, cost-based optimizer, partitioning, RAC, and Data Guard. Use when writing enterprise SQL, managing Oracle schemas, or optimizing heavy OLTP workloads.

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

ErrorCauseSolution
ORA-01555: snapshot too oldRollback segments/undo tablespace overwritten by long-running query.Increase UNDO_RETENTION and resize undo tablespace.
ORA-00054: resource busy and acquire with NOWAIT specifiedTable 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 tablespaceTablespace is full or max datafile size reached.Add a new datafile or enable AUTOEXTEND ON for tablespace.

References

More from this repository10