About

Imported from personamanagmentlayer/pcl (stdlib/domains/oracle-expert/SKILL.md). Install upstream with npx skills add personamanagmentlayer/pcl --skill oracle-expert. Copyright stays with the author.

Oracle Database Expert

Name: oracle-expert
Author: personamanagmentlayer

Core Concepts

Oracle Architecture

Instance - Memory structures (SGA, PGA) and background processes
Database - Physical files (data files, control files, redo logs)
Tablespace - Logical storage container
Schema - Collection of database objects owned by a user
RAC - Real Application Clusters for high availability
Data Guard - Disaster recovery and data protection

PL/SQL Programming

Procedures - Reusable code blocks
Functions - Return value blocks
Packages - Grouped procedures and functions
Triggers - Event-driven code execution
Collections - Arrays and nested tables
Exception Handling - Error management

Performance & Tuning

Execution Plans - Query optimization paths
AWR - Automatic Workload Repository
ASH - Active Session History
Statistics - Cost-based optimizer data
Indexes - B-tree, bitmap, function-based
Partitioning - Data distribution strategies

Best Practices

Database Design

Normalize data to appropriate level (usually 3NF)
Use appropriate data types
Implement proper constraints (PK, FK, CHECK)
Design efficient indexes
Use partitioning for large tables
Implement proper security model

PL/SQL Development

Use bind variables to prevent SQL injection
Implement exception handling
Use bulk operations for better performance
Follow naming conventions
Document code thoroughly
Use packages for code organization

Performance Optimization

Analyze execution plans regularly
Update statistics frequently
Use appropriate indexes
Implement result cache when applicable
Optimize SQL queries before tuning database
Monitor AWR reports

High Availability

Implement Oracle RAC for clustering
Configure Data Guard for disaster recovery
Use RMAN for backup and recovery
Implement flashback technology
Monitor alert logs
Regular testing of recovery procedures

Anti-Patterns

Code Issues

SELECT * in production code
Implicit cursors for large result sets
Missing exception handling
Hard-coded values
Recursive triggers
Autonomous transactions without clear purpose

Performance Problems

Missing indexes on foreign keys
No statistics on tables
Using hints unnecessarily
Lack of bind variables
Full table scans on large tables
Inadequate memory allocation

Design Mistakes

Denormalization without justification
Missing constraints
Improper use of sequences
Inadequate partitioning strategy
No archiving strategy for old data
Mixed OLTP and OLAP workloads

Reference Documentation

Detailed material lives alongside this skill and is read on demand:

Implementation Examples — PL/SQL Package with Complex Logic, Complex Trigger with Business Logic, Performance Tuning Query, RMAN Backup Script

Resources

Official Documentation

Oracle Documentation - Complete documentation
PL/SQL Language Reference - Language guide
Performance Tuning Guide - Tuning reference
Database Concepts - Architecture guide

Learning Platforms

Oracle University - Official training
Oracle Learning Library - Free courses
LiveSQL - Online SQL practice
Oracle Base - Tutorials and articles

Tools & Resources

SQL Developer - Free IDE
Enterprise Manager - Database management
RMAN - Backup and recovery
AWR Reports - Performance analysis

Community Resources

Ask TOM - Q&A by Oracle experts
Oracle Community - Forums
Oracle Blogs - Technical articles
Stack Overflow Oracle Tag - Community help

Use it

Copy one of these into your project. Installing also returns the manifest and these snippets.

yaml

targets:
  - https://api.opensmartroute.ai/api/v1/registry/personamanagmentlayer-pcl-oracle-expert/manifest   # or paste the manifest below

Manifest

An Open Capability Manifest: the router reads it to know what this does, what it costs and when to pick it.

personamanagmentlayer-pcl-oracle-expert.ocm.jsonjson

{
  "ocm": "1",
  "id": "personamanagmentlayer-pcl-oracle-expert",
  "kind": "skill",
  "name": "oracle-expert",
  "description": "Expert in Oracle Database, PL/SQL programming, Oracle RAC, Data Guard, performance tuning, backup/recovery, and enterprise database administration. Use when the user mentions database, enterprise, ERP, PL/SQL, Oracle RAC, or Data Guard, or when the task involves Oracle Architecture, PL/SQL Programming, Performance & Tuning, or PL/SQL Package with Complex Logic.",
  "publisher": "personamanagmentlayer",
  "version": "1.0.0",
  "capabilities": {
    "domains": [
      "coding",
      "data_analysis"
    ],
    "tags": [
      "skill-md",
      "oracle",
      "database",
      "enterprise",
      "erp",
      "plsql",
      "rac",
      "data-guard",
      "performance-tuning",
      "rman"
    ],
    "languages": [
      "en"
    ]
  },
  "quality_prior": 0.6,
  "examples": [
    "Expert in Oracle Database, PL/SQL programming, Oracle RAC, Data Guard, performance tuning, backup/recovery, and enterprise database administration. Use when the user mentions database, enterprise, ERP, PL/SQL, Oracle RAC, or Data Guard, or when the task involves Oracle Architecture, PL/SQL Programming, Performance & Tuning, or PL/SQL Package with Complex Logic."
  ],
  "primary": false,
  "metadata": {
    "source": {
      "provider": "skills.sh",
      "repository": "https://github.com/personamanagmentlayer/pcl",
      "path": "stdlib/domains/oracle-expert/SKILL.md",
      "ref": "HEAD",
      "url": "https://github.com/personamanagmentlayer/pcl/blob/HEAD/stdlib/domains/oracle-expert/SKILL.md",
      "key": "personamanagmentlayer/pcl/stdlib/domains/oracle-expert/SKILL.md"
    },
    "allowed_tools": [
      "Read",
      "Write",
      "Bash",
      "Grep",
      "Glob"
    ]
  },
  "instructions": "# Oracle Database Expert\n\n## Core Concepts\n\n### Oracle Architecture\n\n- **Instance** - Memory structures (SGA, PGA) and background processes\n- **Database** - Physical files (data files, control files, redo logs)\n- **Tablespace** - Logical storage container\n- **Schema** - Collection of database objects owned by a user\n- **RAC** - Real Application Clusters for high availability\n- **Data Guard** - Disaster recovery and data protection\n\n### PL/SQL Programming\n\n- **Procedures** - Reusable code blocks\n- **Functions** - Return value blocks\n- **Packages** - Grouped procedures and functions\n- **Triggers",
  "cost": {
    "context_tokens": 1125
  }
}

Fetch it by URL: GET /api/v1/registry/personamanagmentlayer-pcl-oracle-expert/manifest?version=1.0.0

Reviews

Star ratings from people who tried it. One review per account; edit yours any time.

No reviews yet. Install it, try it, and be the first to rate it.