Oracle

---
name: oracle
description: |
  Oracle Database specific features. Covers data types, sequences, synonyms,
  partitioning, and Oracle-specific SQL syntax. Use for Oracle database work.

  USE WHEN: user mentions "oracle", "oracle database", "sequences", "synonyms",
  "DUAL", "SYSDATE", "NVL", "DECODE", "Oracle partitioning", "Oracle specifics"

  DO NOT USE FOR: PostgreSQL - use `postgresql` instead,
  SQL Server - use `sqlserver` instead, PL/SQL programming - use `plsql` instead
allowed-tools: Read, Grep, Glob, Write, Edit
---

# Oracle Database Core Knowledge

> **Deep Knowledge**: Use `mcp__documentation__fetch_docs` with technology: `oracle` for comprehensive documentation.

## Data Types

| Type | Description | Example |
|------|-------------|---------|
| `NUMBER(p,s)` | Numeric (p=precision, s=scale) | `NUMBER(10,2)` |
| `VARCHAR2(n)` | Variable-length string | `VARCHAR2(100)` |
| `CHAR(n)` | Fixed-length string | `CHAR(10)` |
| `NVARCHAR2(n)` | Unicode variable-length | `NVARCHAR2(100)` |
| `DATE` | Date and time (to seconds) | `DATE` |
| `TIMESTAMP` | Date/time with fractions | `TIMESTAMP(6)` |
| `TIMESTAMP WITH TIME ZONE` | With timezone | `TIMESTAMP WITH TIME ZONE` |
| `CLOB` | Large text (up to 4GB) | `CLOB` |
| `BLOB` | Binary data (up to 4GB) | `BLOB` |
| `RAW(n)` | Binary data (up to 2000) | `RAW(16)` |
| `INTERVAL` | Time interval | `INTERVAL YEAR TO MONTH` |

```sql
CREATE TABLE example (
    id NUMBER(10) PRIMARY KEY,
    code VARCHAR2(20) NOT NULL,
    name NVARCHAR2(100),
    price NUMBER(10,2),
    created_date DATE DEFAULT SYSDATE,
    updated_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    description CLOB,
    data BLOB
);
```

## Sequences

```sql
-- Create sequence
CREATE SEQUENCE emp_seq
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    NOCYCLE
    CACHE 20;

-- Use sequence
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'John');

-- Get current value (must call NEXTVAL first in session)
SELECT emp_seq.CURRVAL FROM DUAL;

-- Reset sequence
ALTER SEQUENCE emp_seq RESTART START WITH 1;

-- Drop sequence
DROP SEQUENCE emp_seq;

-- Identity column (12c+)
CREATE TABLE employees (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(100)
);

-- Or GENERATED BY DEFAULT
CREATE TABLE employees (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100)
);
```

## Synonyms

```sql
-- Private synonym (current schema)
CREATE SYNONYM emp FOR hr.employees;

-- Public synonym (all users)
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- Use synonym
SELECT * FROM emp;

-- Drop synonym
DROP SYNONYM emp;
DROP PUBLIC SYNONYM emp;
```

## DUAL Table

```sql
-- Built-in single-row table for SELECT without FROM
SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
SELECT USER FROM DUAL;
SELECT SYS_GUID() FROM DUAL;  -- Generate UUID

-- Oracle 23c+ allows SELECT without FROM
SELECT SYSDATE;  -- Works in 23c+
```

## Date/Time Functions

```sql
-- Current date/time
SELECT SYSDATE FROM DUAL;                    -- DATE (no timezone)
SELECT SYSTIMESTAMP FROM DUAL;               -- TIMESTAMP WITH TIME ZONE
SELECT CURRENT_DATE FROM DUAL;               -- Session timezone
SELECT CURRENT_TIMESTAMP FROM DUAL;          -- Session timezone

-- Date arithmetic
SELECT SYSDATE + 7 FROM DUAL;                -- Add 7 days
SELECT SYSDATE - 30 FROM DUAL;               -- Subtract 30 days
SELECT date1 - date2 FROM DUAL;              -- Days between dates

-- Add months
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;

-- Truncate date
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;    -- First of month
SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;     -- First of year

-- Format date
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;

-- Parse date
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_TIMESTAMP('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Extract parts
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
```

## String Functions

```sql
-- Concatenation
SELECT 'Hello' || ' ' || 'World' FROM DUAL;
SELECT CONCAT('Hello', ' World') FROM DUAL;  -- Only 2 args

-- Substring
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;  -- 'Hello' (1-based)
SELECT SUBSTR('Hello World', -5) FROM DUAL;    -- 'World' (from end)

-- Length
SELECT LENGTH('Hello') FROM DUAL;              -- 5
SELECT LENGTHB('Hello') FROM DUAL;             -- Bytes

-- Case
SELECT UPPER('hello') FROM DUAL;
SELECT LOWER('HELLO') FROM DUAL;
SELECT INITCAP('hello world') FROM DUAL;       -- 'Hello World'

-- Trim
SELECT TRIM('  hello  ') FROM DUAL;
SELECT LTRIM('  hello') FROM DUAL;
SELECT RTRIM('hello  ') FROM DUAL;
SELECT TRIM('x' FROM 'xxxhelloxxx') FROM DUAL;

-- Replace
SELECT REPLACE('hello', 'l', 'L') FROM DUAL;

-- Padding
SELECT LPAD('123', 10, '0') FROM DUAL;         -- '0000000123'
SELECT RPAD('hello', 10, '.') FROM DUAL;       -- 'hello.....'

-- Position
SELECT INSTR('hello world', 'o') FROM DUAL;    -- 5 (first occurrence)
SELECT INSTR('hello world', 'o', 1, 2) FROM DUAL;  -- 8 (second occurrence)
```

## NULL Handling

```sql
-- NVL: Replace NULL with value
SELECT NVL(commission, 0) FROM employees;

-- NVL2: Different value if NULL vs not NULL
SELECT NVL2(commission, salary + commission, salary) FROM employees;

-- COALESCE: First non-NULL (ANSI SQL)
SELECT COALESCE(phone, mobile, email, 'N/A') FROM contacts;

-- NULLIF: Return NULL if equal
SELECT NULLIF(value1, value2) FROM table1;

-- DECODE (Oracle-specific CASE)
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM users;
```

## Hierarchical Queries

```sql
-- CONNECT BY (Oracle-specific, use recursive CTE in modern code)
SELECT
    employee_id,
    LPAD(' ', 2 * (LEVEL - 1)) || first_name AS name,
    LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY first_name;

-- SYS_CONNECT_BY_PATH
SELECT
    employee_id,
    SYS_CONNECT_BY_PATH(first_name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Modern alternative: Recursive CTE (11g R2+)
WITH hierarchy (employee_id, name, level_num) AS (
    SELECT employee_id, first_name, 1
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, h.level_num + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM hierarchy;
```

## Partitioning

### Range Partitioning

```sql
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2022 VALUES LESS THAN (DATE '2023-01-01'),
    PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION sales_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

-- Interval partitioning (auto-create partitions)
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
    PARTITION p_initial VALUES LESS THAN (DATE '2024-01-01')
);
```

### List Partitioning

```sql
CREATE TABLE orders (
    order_id NUMBER,
    region VARCHAR2(20),
    amount NUMBER(10,2)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES ('NY', 'MA', 'CT'),
    PARTITION p_south VALUES ('FL', 'GA', 'TX'),
    PARTITION p_west VALUES ('CA', 'WA', 'OR'),
    PARTITION p_other VALUES (DEFAULT)
);
```

### Hash Partitioning

```sql
CREATE TABLE customers (
    customer_id NUMBER,
    name VARCHAR2(100)
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;
```

### Partition Management

```sql
-- Add partition
ALTER TABLE sales ADD PARTITION sales_2025
    VALUES LESS THAN (DATE '2026-01-01');

-- Drop partition
ALTER TABLE sales DROP PARTITION sales_2022;

-- Truncate partition
ALTER TABLE sales TRUNCATE PARTITION sales_2022;

-- Split partition
ALTER TABLE sales SPLIT PARTITION sales_future
    AT (DATE '2026-01-01')
    INTO (PARTITION sales_2025, PARTITION sales_future);

-- Merge partitions
ALTER TABLE sales MERGE PARTITIONS sales_2022, sales_2023
    INTO PARTITION sales_old;

-- Exchange partition (swap with table)
ALTER TABLE sales EXCHANGE PARTITION sales_2024
    WITH TABLE sales_2024_staging;
```

## Index Types

```sql
-- B-tree (default)
CREATE INDEX idx_emp_name ON employees(last_name);

-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Composite index
CREATE INDEX idx_emp_dept_name ON employees(department_id, last_name);

-- Function-based index
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- Bitmap index (for low cardinality columns)
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- Reverse key index (reduce contention)
CREATE INDEX idx_emp_id_rev ON employees(employee_id) REVERSE;

-- Invisible index (testing)
CREATE INDEX idx_emp_test ON employees(hire_date) INVISIBLE;
ALTER INDEX idx_emp_test VISIBLE;

-- Local partitioned index
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- Global partitioned index
CREATE INDEX idx_sales_amount ON sales(amount)
    GLOBAL PARTITION BY RANGE (amount) (
        PARTITION p1 VALUES LESS THAN (1000),
        PARTITION p2 VALUES LESS THAN (MAXVALUE)
    );
```

## Analytic Functions

```sql
-- Row number
SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

-- Rank within partition
SELECT
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Running total
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

-- LAG/LEAD
SELECT
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

-- FIRST_VALUE/LAST_VALUE
SELECT
    department_id,
    employee_id,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary
FROM employees;
```

## Materialized Views

```sql
-- Create materialized view
CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
    region,
    product_id,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
FROM sales
GROUP BY region, product_id;

-- Manual refresh
EXEC DBMS_MVIEW.REFRESH('MV_SALES_SUMMARY');

-- Drop
DROP MATERIALIZED VIEW mv_sales_summary;
```

## MERGE Statement

```sql
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.updated_at = SYSDATE
    WHERE s.status = 'ACTIVE'
    DELETE WHERE s.status = 'DELETED'
WHEN NOT MATCHED THEN
    INSERT (id, name, created_at)
    VALUES (s.id, s.name, SYSDATE)
    WHERE s.status != 'DELETED';
```

## Flashback Queries

```sql
-- Query data as of timestamp
SELECT * FROM employees AS OF TIMESTAMP
    TO_TIMESTAMP('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Query data as of SCN
SELECT * FROM employees AS OF SCN 12345678;

-- View row versions
SELECT versions_starttime, versions_endtime, versions_operation, name
FROM employees VERSIONS BETWEEN TIMESTAMP
    SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP
WHERE employee_id = 100;
```

## When NOT to Use This Skill

- **PL/SQL programming** - Use `plsql` skill for stored procedures, packages, triggers
- **PostgreSQL** - Use `postgresql` skill for PostgreSQL-specific features
- **SQL Server** - Use `sqlserver` skill for SQL Server features
- **Basic SQL** - Use `sql-fundamentals` for ANSI SQL basics

## Anti-Patterns

| Anti-Pattern | Problem | Solution |
|--------------|---------|----------|
| Not using bind variables | Hard parsing overhead | Use prepared statements |
| SELECT without ROWNUM limit | Memory issues | Add ROWNUM or FETCH FIRST |
| Implicit data conversions | Performance loss, errors | Use explicit TO_CHAR, TO_NUMBER |
| Not using partitioning for large tables | Slow queries | Implement range/list partitioning |
| Using CONNECT BY for new code | Harder to maintain | Use recursive CTEs (11g+) |
| Ignoring execution plans | Slow queries | Use EXPLAIN PLAN regularly |

## Quick Troubleshooting

| Problem | Diagnostic | Fix |
|---------|------------|-----|
| Slow queries | `EXPLAIN PLAN FOR ...` | Add indexes, rewrite query |
| ORA-01000 max cursors | `SELECT COUNT(*) FROM v$open_cursor` | Increase open_cursors, close cursors |
| ORA-12154 TNS error | Check tnsnames.ora | Fix connection string |
| Sequence gaps | Check cache settings | Use NOCACHE or accept gaps |
| Partition pruning not working | Check WHERE clause | Ensure partition key in filter |

## Reference Documentation

- [Data Types](quick-ref/datatypes.md)
- [Sequences](quick-ref/sequences.md)
- [Partitioning](quick-ref/partitioning.md)