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

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

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
• Sequences
• Partitioning