Oracle

Oracle Database Core Knowledge

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

Data Types

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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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

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

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

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

Partition Management

-- 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

-- 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

-- 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

-- 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

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

-- 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

Quick Troubleshooting

Reference Documentation

• Data Types
• Sequences
• Partitioning