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