1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Connect to database (Easy Connect syntax)
sqlplus username/password@//host:1521/service_name

-- Connect with sys privileges
sqlplus / as sysdba

-- Show current user
SHOW USER;

-- Show current container (CDB/PDB context)
SHOW CON_NAME;

-- Switch to a pluggable database
ALTER SESSION SET CONTAINER = my_pdb;

-- Show current connection details
SELECT sys_context('USERENV', 'CON_NAME'),
       sys_context('USERENV', 'DB_NAME')
FROM dual;

1
2
3
4
5
6
7
8
9
10
11
12
# Start Oracle listener
lsnrctl start

# Stop Oracle listener
lsnrctl stop

# Check listener status
lsnrctl status

# Start the database (from SQL*Plus as sysdba)
# STARTUP;  SHUTDOWN IMMEDIATE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Create a local user inside a PDB
-- (Common users in the CDB root must be prefixed with C##)
CREATE USER enterprise_user IDENTIFIED BY "StrongP@ssw0rd"
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

-- Grant privileges (RESOURCE no longer implies UNLIMITED TABLESPACE since 12c)
GRANT CONNECT, RESOURCE, CREATE VIEW TO enterprise_user;

-- Schema-only (passwordless) account — useful for app schemas (19c+)
CREATE USER app_schema NO AUTHENTICATION;

-- Alter user password
ALTER USER enterprise_user IDENTIFIED BY "NewPassword123";

-- Lock/Unlock user account
ALTER USER enterprise_user ACCOUNT LOCK;
ALTER USER enterprise_user ACCOUNT UNLOCK;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Basic table creation (23ai: IF NOT EXISTS supported)
CREATE TABLE IF NOT EXISTS employees (
    employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name  VARCHAR2(50) NOT NULL,
    last_name   VARCHAR2(50) NOT NULL,
    email       VARCHAR2(100) UNIQUE,
    is_active   BOOLEAN DEFAULT TRUE,         -- 23ai native BOOLEAN
    hire_date   DATE DEFAULT SYSDATE,
    salary      NUMBER(10,2) CHECK (salary > 0)
);

-- Create table with foreign key
CREATE TABLE departments (
    dept_id    NUMBER PRIMARY KEY,
    dept_name  VARCHAR2(100),
    manager_id NUMBER,
    CONSTRAINT fk_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)
);

1
2
3
4
5
6
7
8
9
10
11
12
-- Add column (23ai: IF NOT EXISTS supported)
ALTER TABLE employees ADD (phone_number VARCHAR2(20));

-- Modify column
ALTER TABLE employees MODIFY (email VARCHAR2(150));

-- Drop column
ALTER TABLE employees DROP COLUMN phone_number;

-- Rename table
RENAME employees TO company_employees;

1
2
3
-- Drop table with cascade constraints (23ai: IF EXISTS supported)
DROP TABLE IF EXISTS employees CASCADE CONSTRAINTS;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Common Oracle Data Types
NUMBER(p,s)        -- Numeric with precision and scale
VARCHAR2(size)     -- Variable-length character string (up to 32767 with extended strings)
CHAR(size)         -- Fixed-length character string
DATE               -- Date and time (second precision)
TIMESTAMP          -- More precise date and time (fractional seconds)
TIMESTAMP WITH TIME ZONE
INTERVAL YEAR TO MONTH / DAY TO SECOND
RAW(size)          -- Binary data
CLOB / NCLOB       -- Character large object
BLOB               -- Binary large object
JSON               -- Native JSON type (21c+) — preferred over VARCHAR2/CLOB+IS JSON
BOOLEAN            -- Native SQL BOOLEAN (23ai)
VECTOR(dim, type)  -- AI Vector Search type (23ai), e.g. VECTOR(1536, FLOAT32)
XMLTYPE            -- XML data type

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Basic insert
INSERT INTO employees
(first_name, last_name, email, salary)
VALUES
('John', 'Doe', 'john.doe@company.com', 75000);

-- Multi-row VALUES (23ai table value constructor)
INSERT INTO employees (first_name, last_name, email, salary) VALUES
    ('John', 'Doe',   'john@co.com', 75000),
    ('Jane', 'Smith', 'jane@co.com', 85000);

-- Pre-23ai bulk insert (still valid)
INSERT ALL
    INTO employees (first_name, last_name, email, salary) VALUES ('John','Doe','john@co.com',75000)
    INTO employees (first_name, last_name, email, salary) VALUES ('Jane','Smith','jane@co.com',85000)
SELECT 1 FROM dual;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Basic select
SELECT * FROM employees;

-- Filtered select with bind variables
SELECT first_name, last_name, salary
FROM   employees
WHERE  salary > :min_salary
  AND  department_id = :dept_id;

-- Pagination (Oracle 12c+ row-limiting clause)
SELECT *
FROM   employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Update with condition
UPDATE employees
SET    salary = salary * 1.10
WHERE  performance_rating = 'EXCELLENT';

-- Update with subquery
UPDATE departments d
SET    manager_id = (
    SELECT employee_id
    FROM   employees e
    WHERE  e.last_name = 'Smith'
);

1
2
3
4
5
6
7
-- Delete with condition
DELETE FROM employees
WHERE  hire_date < ADD_MONTHS(SYSDATE, -120);

-- Truncate (faster than delete for entire table)
TRUNCATE TABLE temp_employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Inner Join
SELECT e.first_name, d.dept_name
FROM   employees e
JOIN   departments d ON e.department_id = d.dept_id;

-- Left Outer Join
SELECT e.first_name, d.dept_name
FROM   employees e
LEFT JOIN departments d ON e.department_id = d.dept_id;

-- Full Outer Join
SELECT e.first_name, d.dept_name
FROM   employees e
FULL JOIN departments d ON e.department_id = d.dept_id;

-- Cross Join
SELECT e.first_name, p.product_name
FROM   employees e
CROSS JOIN products p;

-- Lateral / cross apply
SELECT e.first_name, t.top_project
FROM   employees e,
       LATERAL (SELECT project_name AS top_project
                FROM   projects p
                WHERE  p.lead_id = e.employee_id
                FETCH FIRST 1 ROWS ONLY) t;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- B-Tree Index (default)
CREATE INDEX idx_employee_name
ON employees (last_name, first_name);

-- Unique Index
CREATE UNIQUE INDEX idx_unique_email
ON employees (email);

-- Bitmap Index (low-cardinality columns; avoid on heavily DML'd OLTP tables)
CREATE BITMAP INDEX idx_department
ON employees (department_id);

-- Function-based Index
CREATE INDEX idx_emp_upper_last
ON employees (UPPER(last_name));

-- Invisible Index (test impact before going live)
CREATE INDEX idx_emp_dept ON employees(department_id) INVISIBLE;

-- Check Constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary
CHECK (salary BETWEEN 30000 AND 500000);

1
2
3
4
5
6
7
8
SELECT
    UPPER(first_name)         AS upper_name,
    LOWER(last_name)          AS lower_name,
    INITCAP(email)            AS proper_case,
    SUBSTR(first_name, 1, 3)  AS name_prefix,
    REGEXP_REPLACE(phone, '[^0-9]', '') AS digits_only
FROM employees;

1
2
3
4
5
6
7
8
9
SELECT
    SYSDATE,
    SYSTIMESTAMP,
    ADD_MONTHS(hire_date, 12)              AS one_year_anniversary,
    MONTHS_BETWEEN(SYSDATE, hire_date)     AS months_employed,
    LAST_DAY(SYSDATE)                      AS month_end,
    TRUNC(SYSDATE, 'IW')                   AS week_start
FROM employees;

1
2
3
4
5
6
SELECT
    ROUND(salary, -3)     AS salary_rounded,
    TRUNC(salary, 0)      AS salary_truncated,
    MOD(employee_id, 5)   AS employee_group
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Native JSON since 21c; simplified dot-notation and JSON_TABLE
SELECT j.data.customer.name AS customer_name,
       j.data.total         AS order_total
FROM   orders j
WHERE  j.data.status = 'PAID';

-- 23ai JSON Relational Duality View — expose relational data as JSON documents
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW employee_dv AS
SELECT JSON {
    '_id'        : e.employee_id,
    'firstName'  : e.first_name,
    'lastName'   : e.last_name,
    'department' : (SELECT JSON {'id': d.dept_id, 'name': d.dept_name}
                    FROM departments d WHERE d.dept_id = e.department_id)
}
FROM employees e WITH INSERT UPDATE DELETE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SET SERVEROUTPUT ON;

DECLARE
    v_employee_count NUMBER;
    v_avg_salary     NUMBER;
BEGIN
    SELECT COUNT(*), AVG(salary)
    INTO   v_employee_count, v_avg_salary
    FROM   employees;

    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_employee_count);
    DBMS_OUTPUT.PUT_LINE('Average Salary : ' || v_avg_salary);
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE PROCEDURE update_employee_salary (
    p_employee_id      IN NUMBER,
    p_raise_percentage IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET    salary = salary * (1 + p_raise_percentage/100)
    WHERE  employee_id = p_employee_id;

    -- Prefer letting the caller commit; commit here only for autonomous units.
END update_employee_salary;
/

-- Execute procedure
EXEC update_employee_salary(1001, 5);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE TRIGGER trg_audit_employees
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit_log (action, employee_id, change_date)
    VALUES (
        CASE
            WHEN INSERTING THEN 'INSERT'
            WHEN UPDATING THEN 'UPDATE'
            WHEN DELETING THEN 'DELETE'
        END,
        COALESCE(:NEW.employee_id, :OLD.employee_id),
        SYSDATE
    );
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Sequence
CREATE SEQUENCE seq_employee_id
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE;

-- Use in insert
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (seq_employee_id.NEXTVAL, 'New', 'Employee');

-- Modern preferred form: identity column on the table itself (12c+)
-- See CREATE TABLE example in section 3.

1
2
3
4
5
6
7
8
9
10
11
-- Range Partitioning with automatic interval partitions (11g+)
CREATE TABLE sales_data (
    sale_date   DATE,
    product_id  NUMBER,
    amount      NUMBER(10,2)
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
    PARTITION p_seed VALUES LESS THAN (DATE '2024-01-01')
);

1
2
3
4
5
6
7
8
9
10
11
CREATE MATERIALIZED VIEW mv_employee_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT          -- requires materialized view logs
ENABLE QUERY REWRITE
AS
SELECT department_id,
       COUNT(*)    AS employee_count,
       AVG(salary) AS avg_salary
FROM   employees
GROUP BY department_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- CONNECT BY
SELECT employee_id, first_name, last_name, manager_id, LEVEL
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Recursive CTE (ANSI alternative)
WITH org (employee_id, manager_id, full_name, lvl) AS (
    SELECT employee_id, manager_id, first_name||' '||last_name, 1
    FROM   employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.first_name||' '||e.last_name, o.lvl + 1
    FROM   employees e
    JOIN   org o ON e.manager_id = o.employee_id
)
SELECT * FROM org;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Store and search embeddings natively
CREATE TABLE doc_chunks (
    id        NUMBER PRIMARY KEY,
    chunk     CLOB,
    embedding VECTOR(1536, FLOAT32)
);

-- Approximate nearest-neighbor search
SELECT id, chunk
FROM   doc_chunks
ORDER BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

1
2
3
4
5
6
7
8
9
EXPLAIN PLAN FOR
SELECT * FROM employees
WHERE  department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Plan from the cursor cache for a recently executed statement
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'abc123xyz', format => 'ALLSTATS LAST'));

1
2
3
4
5
6
7
8
9
-- Force index usage (use sparingly; prefer good statistics + SQL Plan Management)
SELECT /*+ INDEX(employees idx_employee_name) */
       first_name, last_name
FROM   employees
WHERE  last_name LIKE 'S%';

-- Parallel execution hint
SELECT /*+ PARALLEL(employees, 4) */ COUNT(*) FROM employees;

1
2
3
4
5
-- Gather optimizer statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- Real-time / Automatic SQL Plan Management captures and evolves plans automatically.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Create Role
CREATE ROLE enterprise_read_only;

-- Grant privileges to role
GRANT SELECT ON employees TO enterprise_read_only;

-- Create user with role and a mandatory password profile
CREATE USER readonly_user
IDENTIFIED BY "ReadOnlyP@ss123"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE app_profile;

GRANT enterprise_read_only TO readonly_user;

-- Transparent Data Encryption (column-level)
ALTER TABLE employees MODIFY (salary ENCRYPT USING 'AES256');

-- SQL Firewall (23ai) — capture and enforce allow-listed SQL per user
EXEC DBMS_SQL_FIREWALL.ENABLE;
EXEC DBMS_SQL_FIREWALL.CREATE_CAPTURE(username => 'APP_USER', top_level_only => TRUE);