oracle__skills/skills-index.md

Oracle DB Skills Index

Repository: https://github.com/krisrice/oracle-db-skills Version: 1.0.0

A tracking file for skills.md topics to create for working with Oracle DB.

Status Legend

• Not started
• Complete

---

Database Design & Modeling

• erd-design.md — Entity relationship design, normalization, naming conventions
• data-modeling.md — Logical vs physical modeling, star/snowflake schemas
• partitioning-strategy.md — Range, list, hash, composite partitioning
• tablespace-design.md — Sizing, storage layout, bigfile vs smallfile

SQL Development

• sql-tuning.md — Execution plans, hints, optimizer statistics
• sql-injection-avoidance.md — Bind variables, dynamic SQL safety, DBMS_ASSERT
• pl-sql-best-practices.md — Bulk operations, exception handling, cursor management
• sql-patterns.md — Window functions, CTEs, CONNECT BY, MODEL clause
• dynamic-sql.md — EXECUTE IMMEDIATE, DBMS_SQL, safe patterns

Performance & Tuning

• awr-reports.md — Reading AWR, key metrics, Top SQL, wait events
• ash-analysis.md — Active Session History, real-time tuning
• explain-plan.md — Reading execution plans, DBMS_XPLAN, autotrace
• index-strategy.md — B-tree, bitmap, function-based, invisible indexes
• optimizer-stats.md — DBMS_STATS, histograms, extended statistics
• wait-events.md — Common wait events, diagnosis, remediation
• memory-tuning.md — SGA, PGA, buffer cache, shared pool sizing

Application Development

• connection-pooling.md — UCP, DRCP, connection best practices
• transaction-management.md — Commit frequency, savepoints, autonomous transactions
• locking-concurrency.md — Row locks, deadlocks, NOWAIT/SKIP LOCKED
• sequences-identity.md — Sequence caching, identity columns, UUID alternatives
• json-in-oracle.md — JSON data type, JSON_TABLE, dot notation, indexes
• xml-in-oracle.md — XMLType, XQuery, XML indexes
• spatial-data.md — SDO_GEOMETRY, spatial indexes, query patterns
• oracle-text.md — Full-text search, CONTEXT indexes, CONTAINS queries
• sql-property-graph.md — SQL Property Graph DDL, GRAPH_TABLE, MATCH patterns, quantified paths (23ai+)
• python-oracledb.md — python-oracledb driver, thin/thick mode, bind variables, pooling, async
• java-oracle-jdbc.md — JDBC thin driver, UCP, PreparedStatement, batch, Spring Boot
• nodejs-oracledb.md — node-oracledb driver, async/await, pools, result sets, LOBs
• dotnet-oracle.md — ODP.NET managed driver, EF Core, array binding, OracleParameter
• golang-oracle.md — godror driver, database/sql interface, named binds, REF CURSORs

Security

• privilege-management.md — Least privilege, roles, system vs object privileges
• row-level-security.md — VPD/FGAC, RLS policies, application contexts
• data-masking.md — Oracle Data Masking, redaction policies
• auditing.md — Unified auditing, fine-grained auditing, audit policies
• encryption.md — TDE, column encryption, wallet management
• network-security.md — SSL/TLS, ACLs for network access

Administration

• backup-recovery.md — RMAN strategy, backup sets, recovery scenarios
• dataguard.md — Standby setup, switchover, failover, lag monitoring
• rman-basics.md — Common RMAN commands, incremental backups, catalog
• undo-management.md — Undo retention, ORA-01555 avoidance, sizing
• redo-log-management.md — Log sizing, archivelog mode, log switches
• user-management.md — Creating users, profiles, password policies

Monitoring & Diagnostics

• alert-log-analysis.md — Common errors, patterns, ORA- error reference
• adrci-usage.md — ADR repository, incident investigation
• health-monitor.md — DB health checks, DBMS_HM, advisors
• space-management.md — Segment advisor, reclaiming space, HWM
• top-sql-queries.md — Finding expensive SQL, V$ views for diagnostics

Architecture & Infrastructure

• rac-concepts.md — Real Application Clusters, interconnect, services
• multitenant.md — CDB/PDB architecture, pluggable databases, cloning
• oracle-cloud-oci.md — ATP, ADW, Exadata Cloud, cloud-specific features
• exadata-features.md — Smart Scan, Storage Indexes, Hybrid Columnar Compression
• inmemory-column-store.md — In-Memory option, population, analytics queries

DevOps & CI/CD

• schema-migrations.md — Liquibase, Flyway, online redefinition
• online-operations.md — DBMS_REDEFINITION, online index rebuild, edition-based redefinition
• edition-based-redefinition.md — EBR for zero-downtime deployments
• database-testing.md — utPLSQL, test data management, TDD for PL/SQL
• version-control-sql.md — Source controlling schema objects, DDL extraction

Migrations to Oracle

• migrate-postgres-to-oracle.md — Data type mapping, SQL dialect differences, sequence/serial conversion, psql vs sqlplus
• migrate-mysql-to-oracle.md — AUTO_INCREMENT, data types, stored proc syntax, LIMIT/OFFSET to ROWNUM/FETCH
• migrate-redshift-to-oracle.md — Distribution/sort keys, Redshift SQL quirks, COPY command equivalents, columnar to row-based considerations
• migrate-sqlserver-to-oracle.md — T-SQL to PL/SQL, identity columns, TOP to FETCH, linked servers to DBLinks
• migrate-db2-to-oracle.md — DB2 SQL dialect, REORG equivalents, package concepts
• migrate-sqlite-to-oracle.md — Type affinity mapping, lightweight to enterprise patterns
• migrate-mongodb-to-oracle.md — Document model to relational/JSON duality, aggregation pipeline to SQL
• migrate-snowflake-to-oracle.md — Warehouse/schema mapping, Snowflake SQL dialect, semi-structured data
• migrate-teradata-to-oracle.md — Teradata SQL dialect, BTEQ scripts, multiset tables, partitioning differences
• migrate-sybase-to-oracle.md — ASE to Oracle type mapping, Sybase stored proc conversion
• oracle-migration-tools.md — SQL Developer Migration Workbench, AWS SCT, SSMA, ora2pg tool guide
• migration-assessment.md — Pre-migration assessment, complexity scoring, risk identification, effort estimation
• migration-data-validation.md — Row count checks, hash comparisons, data drift detection, reconciliation scripts
• migration-cutover-strategy.md — Cutover planning, parallel run, go/no-go criteria, rollback planning

PL/SQL Development

• plsql-package-design.md — Package architecture, spec vs body, public/private APIs, cohesion, initialization blocks
• plsql-error-handling.md — Exception hierarchy, named exceptions, PRAGMA EXCEPTION_INIT, SQLERRM/DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, error logging patterns, re-raising
• plsql-performance.md — Context switch minimization, BULK COLLECT/FORALL, pipelined functions, NOCOPY, result cache (RESULT_CACHE), deterministic functions
• plsql-collections.md — Associative arrays, nested tables, varrays — declarations, methods (COUNT, FIRST, LAST, NEXT, DELETE), bulk operations, TABLE() function
• plsql-cursors.md — Implicit vs explicit cursors, cursor FOR loops, parameterized cursors, REF CURSORs (strong/weak), SYS_REFCURSOR, cursor variables across call boundaries
• plsql-security.md — AUTHID CURRENT_USER vs DEFINER rights, SQL injection in PL/SQL, DBMS_ASSERT, secure coding checklist
• plsql-debugging.md — DBMS_OUTPUT, DBMS_APPLICATION_INFO, SQL Developer debugger, compile warnings (PLSQL_WARNINGS), runtime errors tracing
• plsql-patterns.md — Row pipelining, autonomous transactions for logging, table API (TAPI) pattern, object types in PL/SQL, PL/SQL records
• plsql-compiler-options.md — PLSQL_OPTIMIZE_LEVEL, PLSQL_CODE_TYPE (native vs interpreted), conditional compilation ($$PLSQL_LINE, $IF), edition-based compilation
• plsql-code-quality.md — Naming conventions, code review checklist, avoiding anti-patterns (WHEN OTHERS NULL, hardcoded literals, magic numbers), static analysis with PL/SQL Cop / Trivadis guidelines

SQLcl

• sqlcl-basics.md — Installation, connecting, basic commands, differences from SQL*Plus
• sqlcl-scripting.md — JavaScript scripting engine (Nashorn/GraalVM), script command, automating tasks
• sqlcl-liquibase.md — Built-in Liquibase integration, lb generate-schema, lb update, changelogs from SQLcl
• sqlcl-formatting.md — SET commands, column formatting, output formats (CSV, JSON, XML, INSERT, LOADER), ANSICONSOLE
• sqlcl-ddl-generation.md — DDL command, exporting schema objects, options for clean DDL output
• sqlcl-data-loading.md — LOAD command for CSV ingestion, format options, error handling
• sqlcl-cicd.md — Using SQLcl in CI/CD pipelines, headless/non-interactive mode, exit codes, wallet connections
• sqlcl-mcp-server.md — SQLcl MCP server setup, connecting AI assistants to Oracle DB, available tools, security considerations

ORDS (Oracle REST Data Services)

• ords-architecture.md — ORDS deployment models (standalone, Tomcat, WebLogic), connection pools, ORDS schema, request routing
• ords-installation.md — Installing and configuring ORDS, pool configuration, wallet setup for ATP/ADW, upgrading
• ords-auto-rest.md — AutoREST enabling tables/views, generated endpoint patterns, filtering, pagination, ordering
• ords-rest-api-design.md — ORDS.DEFINE_MODULE/TEMPLATE/HANDLER, HTTP methods, bind parameters, implicit/explicit parameters
• ords-authentication.md — OAuth2 flows (client credentials, auth code), privilege definitions, role mapping, JWT validation
• ords-pl-sql-gateway.md — Calling PL/SQL from REST, OUT parameters, result sets via REF CURSORs, APEX_JSON, error handling
• ords-file-upload-download.md — BLOB upload/download endpoints, multipart form data, content-type handling
• ords-metadata-catalog.md — OpenAPI/Swagger document generation, metadata endpoint, documenting APIs
• ords-security.md — Securing endpoints, HTTPS, CORS configuration, rate limiting, allowed origins, privilege checking
• ords-monitoring.md — ORDS logs, request logging, performance tuning, connection pool monitoring, error diagnosis

Frameworks

• sqlalchemy-oracle.md — SQLAlchemy ORM/Core dialect, engine setup, models, sequences, bulk ops
• django-oracle.md — Django ORM backend, settings, migrations, Oracle quirks (empty string, ROWNUM)
• pandas-oracle.md — read_sql, to_sql, chunked reads, bulk load via executemany, dtype mapping
• spring-data-jpa-oracle.md — Hibernate Oracle dialect, @SequenceGenerator, native queries, PL/SQL calls
• mybatis-oracle.md — Mapper XML, #{} binds, dynamic SQL, CALLABLE statements, sequences
• typeorm-oracle.md — TypeORM entities, QueryBuilder, migrations, NestJS integration
• sequelize-oracle.md — Sequelize model definition, field mapping, sequence hooks, raw SQL
• dapper-oracle.md — Dapper Query, DynamicParameters, OUT params, multi-mapping
• gorm-oracle.md — GORM models, BeforeCreate sequence hook, scopes, transactions

Oracle-Specific Features

• advanced-queuing.md — AQ/TQ messaging, producers/consumers
• dbms-scheduler.md — Job scheduling, chains, event-based jobs
• virtual-columns.md — Virtual columns, function-based patterns
• materialized-views.md — MV refresh strategies, query rewrite
• database-links.md — DBLinks, distributed queries, risks
• oracle-apex.md — Low-code app dev on Oracle
• vector-search.md — VECTOR type, HNSW/IVF indexes, VECTOR_DISTANCE(), RAG patterns (26ai)
• select-ai.md — SELECT AI natural language to SQL, AI profiles, actions (26ai)
• dbms-vector.md — DBMS_VECTOR and DBMS_VECTOR_CHAIN, embedding pipelines (26ai)
• ai-profiles.md — AI provider profile configuration: OpenAI, Cohere, OCI GenAI, Anthropic (26ai)

Agent & AI Assistant Patterns

• safe-dml-patterns.md — Always-WHERE guards, dry run, count before delete, bulk DML safety
• destructive-op-guards.md — Pre-flight checks before DROP/TRUNCATE: dependencies, locks, DDL snapshot
• idempotency-patterns.md — MERGE over INSERT, CREATE OR REPLACE, retry-safe migration patterns
• nl-to-sql-patterns.md — NL phrasings mapped to Oracle SQL constructs
• schema-discovery.md — Agent startup queries: tables, columns, constraints, privileges
• intent-disambiguation.md — When to ask for clarification; safe defaults; question templates
• client-identification.md — DBMS_APPLICATION_INFO, CLIENT_IDENTIFIER for agent traceability
• ora-error-catalog.md — Top 25 ORA- errors with root cause and corrective action