Oracle Database Cheatsheet

Oracle Database - Enterprise Database Management System

Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation. It is one of the most widely used enterprise database systems, known for its reliability, scalability, and comprehensive feature set.

📋 Copy All Commands 📄 Generate PDF

Table of Contents

• Installation
• Basic Commands
• Database Operations
• Schema and User Management
• Table Operations
• Data Types
• CRUD Operations
• Query Operations
• Indexes
• Views
• Stored Procedures
• Functions
• Packages
• Triggers
• Transactions
• PL/SQL
• Backup and Recovery
• Performance Tuning
• Monitoring
• Security
• Best Practices

Installation

Oracle Database Installation (Linux)

# Download Oracle Database from Oracle website
# https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

# Install prerequisites (Oracle Linux/RHEL)
sudo yum install -y oracle-database-preinstall-19c

# Extract installation files
unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME

# Run installer
cd $ORACLE_HOME
./runInstaller

# Silent installation example
./runInstaller -silent \
  -responseFile /path/to/db_install.rsp \
  oracle.install.option=INSTALL_DB_SWONLY \
  ORACLE_HOSTNAME=hostname \
  UNIX_GROUP_NAME=oinstall \
  INVENTORY_LOCATION=/u01/app/oraInventory \
  ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 \
  ORACLE_BASE=/u01/app/oracle \
  oracle.install.db.InstallEdition=EE \
  oracle.install.db.OSDBA_GROUP=dba \
  oracle.install.db.OSOPER_GROUP=oper \
  oracle.install.db.OSBACKUPDBA_GROUP=backupdba \
  oracle.install.db.OSDGDBA_GROUP=dgdba \
  oracle.install.db.OSKMDBA_GROUP=kmdba \
  oracle.install.db.OSRACDBA_GROUP=racdba \
  DECLINE_SECURITY_UPDATES=true

Oracle Database Express Edition (XE)

# Download Oracle XE
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm

# Install Oracle XE
sudo yum localinstall oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm

# Configure Oracle XE
sudo /etc/init.d/oracle-xe-21c configure

# Set environment variables
echo 'export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE' >> ~/.bashrc
echo 'export ORACLE_SID=XE' >> ~/.bashrc
echo 'export PATH=$ORACLE_HOME/bin:$PATH' >> ~/.bashrc
source ~/.bashrc

# Connect to Oracle
sqlplus sys as sysdba

Docker Installation

# Pull Oracle Database image
docker pull container-registry.oracle.com/database/express:21.3.0-xe

# Run Oracle XE container
docker run --name oracle-xe \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PWD=password \
  -e ORACLE_CHARACTERSET=AL32UTF8 \
  -v oracle-data:/opt/oracle/oradata \
  -d container-registry.oracle.com/database/express:21.3.0-xe

# Connect to Oracle in container
docker exec -it oracle-xe sqlplus sys/password@localhost:1521/XE as sysdba

Basic Commands

Connecting to Oracle

# Connect as SYSDBA
sqlplus sys as sysdba

# Connect with username and password
sqlplus username/password

# Connect to specific database
sqlplus username/password@hostname:1521/service_name

# Connect using TNS alias
sqlplus username/password@tns_alias

# Connect using Easy Connect
sqlplus username/password@//hostname:1521/service_name

# Silent connection
sqlplus -s username/password

SQL*Plus Commands

-- Show user
SHOW USER;

-- Show current database
SELECT name FROM v$database;

-- Show instance
SELECT instance_name FROM v$instance;

-- Set display options
SET PAGESIZE 50;
SET LINESIZE 120;
SET TIMING ON;
SET AUTOTRACE ON;

-- Describe table
DESC table_name;
DESCRIBE table_name;

-- Show errors
SHOW ERRORS;

-- Spool output to file
SPOOL output.txt;
-- Your SQL commands here
SPOOL OFF;

-- Execute script
@script.sql;
START script.sql;

-- Exit SQL*Plus
EXIT;
QUIT;

System Information

-- Oracle version
SELECT * FROM v$version;

-- Database information
SELECT name, created, log_mode FROM v$database;

-- Instance information
SELECT instance_name, host_name, version, startup_time 
FROM v$instance;

-- Show parameters
SHOW PARAMETER db_name;
SHOW PARAMETER memory;

-- Show SGA information
SELECT * FROM v$sga;

-- Show tablespaces
SELECT tablespace_name, status FROM dba_tablespaces;

-- Show datafiles
SELECT file_name, tablespace_name, bytes/1024/1024 AS mb 
FROM dba_data_files;

Database Operations

Database Creation

-- Create database (using DBCA is recommended)
CREATE DATABASE mydb
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mydb/redo01.log') SIZE 100M,
          GROUP 2 ('/u01/app/oracle/oradata/mydb/redo02.log') SIZE 100M,
          GROUP 3 ('/u01/app/oracle/oradata/mydb/redo03.log') SIZE 100M
  MAXLOGFILES 5
  MAXLOGMEMBERS 5
  MAXLOGHISTORY 1
  MAXDATAFILES 100
  MAXINSTANCES 1
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  DATAFILE '/u01/app/oracle/oradata/mydb/system01.dbf' SIZE 500M REUSE
  EXTENT MANAGEMENT LOCAL
  SYSAUX DATAFILE '/u01/app/oracle/oradata/mydb/sysaux01.dbf' SIZE 500M REUSE
  DEFAULT TABLESPACE users
    DATAFILE '/u01/app/oracle/oradata/mydb/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON
  DEFAULT TEMPORARY TABLESPACE temp
    TEMPFILE '/u01/app/oracle/oradata/mydb/temp01.dbf' SIZE 100M REUSE
  UNDO TABLESPACE undotbs1
    DATAFILE '/u01/app/oracle/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON;

Tablespace Management

-- Create tablespace
CREATE TABLESPACE app_data
  DATAFILE '/u01/app/oracle/oradata/mydb/app_data01.dbf' 
  SIZE 100M 
  AUTOEXTEND ON 
  NEXT 10M 
  MAXSIZE UNLIMITED;

-- Create temporary tablespace
CREATE TEMPORARY TABLESPACE temp_large
  TEMPFILE '/u01/app/oracle/oradata/mydb/temp_large01.dbf' 
  SIZE 100M 
  AUTOEXTEND ON;

-- Add datafile to tablespace
ALTER TABLESPACE app_data 
ADD DATAFILE '/u01/app/oracle/oradata/mydb/app_data02.dbf' 
SIZE 100M AUTOEXTEND ON;

-- Resize datafile
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/app_data01.dbf' 
RESIZE 200M;

-- Take tablespace offline/online
ALTER TABLESPACE app_data OFFLINE;
ALTER TABLESPACE app_data ONLINE;

-- Drop tablespace
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;

-- Show tablespace usage
SELECT 
    tablespace_name,
    ROUND(bytes/1024/1024, 2) AS total_mb,
    ROUND(maxbytes/1024/1024, 2) AS max_mb
FROM dba_data_files
ORDER BY tablespace_name;

Schema and User Management

Creating Users

-- Create user
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON app_data;

-- Create user with profile
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  PROFILE default_profile
  QUOTA 100M ON app_data;

-- Alter user
ALTER USER app_user IDENTIFIED BY new_password;
ALTER USER app_user QUOTA UNLIMITED ON app_data;
ALTER USER app_user DEFAULT TABLESPACE new_tablespace;

-- Lock/unlock user
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;

-- Drop user
DROP USER app_user CASCADE;

Granting Privileges

-- System privileges
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;
GRANT CREATE PROCEDURE TO app_user;
GRANT CREATE SEQUENCE TO app_user;

-- Object privileges
GRANT SELECT ON hr.employees TO app_user;
GRANT INSERT, UPDATE, DELETE ON hr.employees TO app_user;
GRANT ALL ON hr.employees TO app_user;

-- Grant with admin option
GRANT CREATE USER TO app_user WITH ADMIN OPTION;

-- Grant with grant option
GRANT SELECT ON hr.employees TO app_user WITH GRANT OPTION;

-- Revoke privileges
REVOKE CREATE TABLE FROM app_user;
REVOKE SELECT ON hr.employees FROM app_user;

Role Management

-- Create role
CREATE ROLE app_role;

-- Grant privileges to role
GRANT CREATE SESSION, CREATE TABLE TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_role;

-- Grant role to user
GRANT app_role TO app_user;

-- Set default role
ALTER USER app_user DEFAULT ROLE app_role;

-- Enable/disable role
SET ROLE app_role;
SET ROLE ALL;
SET ROLE NONE;

-- Drop role
DROP ROLE app_role;

-- Show user privileges
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;
SELECT * FROM user_role_privs;

-- Show role privileges
SELECT * FROM role_sys_privs WHERE role = 'APP_ROLE';
SELECT * FROM role_tab_privs WHERE role = 'APP_ROLE';

Table Operations

Creating Tables

-- Basic table creation
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) UNIQUE,
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with constraints
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    manager_id NUMBER(6),
    location_id NUMBER(4),
    CONSTRAINT dept_name_unique UNIQUE (department_name),
    CONSTRAINT dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- Table with check constraints
CREATE TABLE products (
    product_id NUMBER(6) PRIMARY KEY,
    product_name VARCHAR2(50) NOT NULL,
    price NUMBER(8,2) CHECK (price > 0),
    category VARCHAR2(20) CHECK (category IN ('Electronics', 'Clothing', 'Books')),
    stock_quantity NUMBER(6) DEFAULT 0 CHECK (stock_quantity >= 0)
);

-- Partitioned table
CREATE TABLE sales (
    sale_id NUMBER(10) PRIMARY KEY,
    sale_date DATE,
    customer_id NUMBER(6),
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2023_q1 VALUES LESS THAN (DATE '2023-04-01'),
    PARTITION sales_2023_q2 VALUES LESS THAN (DATE '2023-07-01'),
    PARTITION sales_2023_q3 VALUES LESS THAN (DATE '2023-10-01'),
    PARTITION sales_2023_q4 VALUES LESS THAN (DATE '2024-01-01')
);

-- Index-organized table
CREATE TABLE lookup_data (
    code VARCHAR2(10) PRIMARY KEY,
    description VARCHAR2(100),
    value NUMBER
) ORGANIZATION INDEX;

Modifying Tables

-- Add column
ALTER TABLE employees ADD (middle_name VARCHAR2(20));

-- Modify column
ALTER TABLE employees MODIFY (salary NUMBER(10,2));

-- Drop column
ALTER TABLE employees DROP COLUMN middle_name;

-- Rename column
ALTER TABLE employees RENAME COLUMN phone_number TO phone;

-- Add constraint
ALTER TABLE employees ADD CONSTRAINT emp_salary_check CHECK (salary > 0);

-- Drop constraint
ALTER TABLE employees DROP CONSTRAINT emp_salary_check;

-- Enable/disable constraint
ALTER TABLE employees DISABLE CONSTRAINT emp_salary_check;
ALTER TABLE employees ENABLE CONSTRAINT emp_salary_check;

-- Rename table
ALTER TABLE employees RENAME TO staff;

-- Move table to different tablespace
ALTER TABLE employees MOVE TABLESPACE new_tablespace;

-- Drop table
DROP TABLE employees;
DROP TABLE employees CASCADE CONSTRAINTS;

Table Information

-- Describe table
DESC employees;

-- Show table structure
SELECT column_name, data_type, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

-- Show constraints
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

-- Show indexes
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- Show table statistics
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables
WHERE table_name = 'EMPLOYEES';

Data Types

Numeric Data Types

-- Number types
NUMBER              -- Variable precision and scale
NUMBER(p)           -- Precision p, scale 0
NUMBER(p,s)         -- Precision p, scale s
INTEGER             -- Synonym for NUMBER(38)
INT                 -- Synonym for NUMBER(38)
SMALLINT            -- Synonym for NUMBER(38)
DECIMAL(p,s)        -- Synonym for NUMBER(p,s)
NUMERIC(p,s)        -- Synonym for NUMBER(p,s)
FLOAT               -- Synonym for NUMBER
REAL                -- Synonym for NUMBER
DOUBLE PRECISION    -- Synonym for NUMBER

-- Binary floating-point types
BINARY_FLOAT        -- 32-bit floating point
BINARY_DOUBLE       -- 64-bit floating point

-- Examples
CREATE TABLE numeric_examples (
    id NUMBER(10) PRIMARY KEY,
    price NUMBER(8,2),
    quantity INTEGER,
    percentage NUMBER(5,2),
    scientific_value BINARY_DOUBLE
);

Character Data Types

-- Character types
CHAR(size)          -- Fixed-length character string
VARCHAR2(size)      -- Variable-length character string
NCHAR(size)         -- Fixed-length Unicode character string
NVARCHAR2(size)     -- Variable-length Unicode character string
CLOB                -- Character Large Object (up to 4GB)
NCLOB               -- Unicode Character Large Object

-- Examples
CREATE TABLE character_examples (
    id NUMBER PRIMARY KEY,
    code CHAR(5),
    name VARCHAR2(100),
    description CLOB,
    unicode_text NVARCHAR2(200)
);

Date and Time Data Types

-- Date and time types
DATE                -- Date and time (no fractional seconds)
TIMESTAMP           -- Date and time with fractional seconds
TIMESTAMP(precision) -- Date and time with specified precision
TIMESTAMP WITH TIME ZONE        -- Timestamp with timezone
TIMESTAMP WITH LOCAL TIME ZONE  -- Timestamp normalized to database timezone
INTERVAL YEAR TO MONTH          -- Year-month interval
INTERVAL DAY TO SECOND          -- Day-second interval

-- Examples
CREATE TABLE datetime_examples (
    id NUMBER PRIMARY KEY,
    birth_date DATE,
    created_at TIMESTAMP,
    precise_time TIMESTAMP(6),
    global_time TIMESTAMP WITH TIME ZONE,
    local_time TIMESTAMP WITH LOCAL TIME ZONE,
    age_interval INTERVAL YEAR TO MONTH,
    duration INTERVAL DAY TO SECOND
);

Other Data Types

-- Binary data types
RAW(size)           -- Variable-length binary data
LONG RAW            -- Variable-length binary data (deprecated)
BLOB                -- Binary Large Object

-- Row identifier
ROWID               -- Physical row identifier
UROWID              -- Universal row identifier

-- XML data type
XMLType             -- XML documents

-- Examples
CREATE TABLE other_examples (
    id NUMBER PRIMARY KEY,
    binary_data RAW(2000),
    large_binary BLOB,
    xml_document XMLType,
    row_identifier ROWID
);

CRUD Operations

Insert Operations

-- Insert single row
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', 'john.doe@company.com', SYSDATE);

-- Insert multiple rows
INSERT ALL
  INTO employees VALUES (1002, 'Jane', 'Smith', 'jane.smith@company.com', SYSDATE)
  INTO employees VALUES (1003, 'Bob', 'Johnson', 'bob.johnson@company.com', SYSDATE)
SELECT * FROM dual;

-- Insert from select
INSERT INTO employee_backup
SELECT * FROM employees WHERE hire_date < DATE '2020-01-01';

-- Insert with returning clause
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1004, 'Alice', 'Brown', 'alice.brown@company.com', SYSDATE)
RETURNING employee_id INTO :new_id;

-- Conditional insert
INSERT ALL
  WHEN salary > 10000 THEN
    INTO high_earners VALUES (employee_id, first_name, last_name, salary)
  WHEN salary BETWEEN 5000 AND 10000 THEN
    INTO mid_earners VALUES (employee_id, first_name, last_name, salary)
  ELSE
    INTO low_earners VALUES (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary FROM employees;

Select Operations

-- Basic select
SELECT * FROM employees;

-- Select with specific columns
SELECT employee_id, first_name, last_name, salary FROM employees;

-- Select with alias
SELECT employee_id AS id, first_name || ' ' || last_name AS full_name
FROM employees;

-- Select with WHERE clause
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM employees WHERE first_name LIKE 'J%';
SELECT * FROM employees WHERE hire_date > DATE '2020-01-01';

-- Select with ORDER BY
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department_id, salary DESC;

-- Select with GROUP BY
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;

-- Select with HAVING
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- Select with ROWNUM (Oracle-specific pagination)
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

-- Select with OFFSET/FETCH (Oracle 12c+)
SELECT * FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Update Operations

-- Update single row
UPDATE employees 
SET salary = 6000 
WHERE employee_id = 1001;

-- Update multiple columns
UPDATE employees 
SET salary = salary * 1.1, 
    commission_pct = 0.05 
WHERE department_id = 10;

-- Update with subquery
UPDATE employees 
SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id)
WHERE salary IS NULL;

-- Update with join (using merge)
MERGE INTO employees e
USING (SELECT employee_id, new_salary FROM salary_updates) s
ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET salary = s.new_salary;

-- Update with returning clause
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 10
RETURNING employee_id, salary INTO :emp_id, :new_salary;

Delete Operations

-- Delete specific rows
DELETE FROM employees WHERE employee_id = 1001;

-- Delete with conditions
DELETE FROM employees WHERE hire_date < DATE '2010-01-01';

-- Delete with subquery
DELETE FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- Delete all rows
DELETE FROM employees;

-- Truncate table (faster than delete)
TRUNCATE TABLE employees;

-- Delete with returning clause
DELETE FROM employees 
WHERE department_id = 10
RETURNING employee_id, first_name, last_name INTO :emp_id, :fname, :lname;

Query Operations

Joins

-- Inner join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- Left outer join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

-- Right outer join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

-- Full outer join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- Cross join
SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

-- Self join
SELECT e1.first_name || ' ' || e1.last_name AS employee,
       e2.first_name || ' ' || e2.last_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Subqueries

-- Subquery in WHERE clause
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery with IN
SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- Subquery with EXISTS
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);

-- Correlated subquery
SELECT * FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);

-- Subquery in SELECT clause
SELECT employee_id, first_name, last_name,
       (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS dept_name
FROM employees e;

-- Subquery in FROM clause
SELECT dept_name, avg_salary
FROM (SELECT d.department_name AS dept_name, AVG(e.salary) AS avg_salary
      FROM employees e JOIN departments d ON e.department_id = d.department_id
      GROUP BY d.department_name);

Analytical Functions

-- ROW_NUMBER
SELECT employee_id, first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- RANK and DENSE_RANK
SELECT employee_id, first_name, last_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- PARTITION BY
SELECT employee_id, first_name, last_name, department_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG and LEAD
SELECT employee_id, hire_date, salary,
       LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

-- FIRST_VALUE and LAST_VALUE
SELECT employee_id, department_id, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS min_salary,
       LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary 
                                RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_salary
FROM employees;

-- Running totals
SELECT employee_id, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM employees;

-- Moving averages
SELECT employee_id, hire_date, salary,
       AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

Hierarchical Queries

-- Connect by (Oracle-specific)
SELECT LEVEL, employee_id, first_name, last_name, manager_id,
       LPAD(' ', (LEVEL-1)*2) || first_name || ' ' || last_name AS hierarchy
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

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

-- Recursive CTE (Oracle 11g R2+)
WITH emp_hierarchy (employee_id, first_name, last_name, manager_id, level_num) AS (
  -- Anchor member
  SELECT employee_id, first_name, last_name, manager_id, 1
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member
  SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level_num + 1
  FROM employees e
  JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy;

Indexes

Creating Indexes

-- Simple index
CREATE INDEX idx_emp_last_name ON employees(last_name);

-- Composite index
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

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

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

-- Partial index (with WHERE clause)
CREATE INDEX idx_emp_active ON employees(employee_id) WHERE status = 'ACTIVE';

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

-- Reverse key index
CREATE INDEX idx_emp_id_reverse ON employees(employee_id) REVERSE;

-- Compressed index
CREATE INDEX idx_emp_dept_job COMPRESS ON employees(department_id, job_id);

-- Invisible index (Oracle 11g+)
CREATE INDEX idx_emp_hire_date ON employees(hire_date) INVISIBLE;

Managing Indexes

-- Show indexes
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- Show index columns
SELECT index_name, column_name, column_position, descend
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- Rebuild index
ALTER INDEX idx_emp_last_name REBUILD;

-- Rebuild index online
ALTER INDEX idx_emp_last_name REBUILD ONLINE;

-- Make index visible/invisible
ALTER INDEX idx_emp_hire_date VISIBLE;
ALTER INDEX idx_emp_hire_date INVISIBLE;

-- Drop index
DROP INDEX idx_emp_last_name;

-- Analyze index
ANALYZE INDEX idx_emp_last_name VALIDATE STRUCTURE;

-- Monitor index usage
ALTER INDEX idx_emp_last_name MONITORING USAGE;
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_LAST_NAME';

Views

Creating Views

-- Simple view
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, email, department_id
FROM employees
WHERE status = 'ACTIVE';

-- Complex view with joins
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.salary,
       d.department_name, d.location_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- View with check option
CREATE VIEW high_salary_emp AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000
WITH CHECK OPTION;

-- Read-only view
CREATE VIEW emp_summary AS
SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
WITH READ ONLY;

-- Materialized view
CREATE MATERIALIZED VIEW emp_dept_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT d.department_name, COUNT(*) AS emp_count, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Managing Views

-- Show views
SELECT view_name, text FROM user_views;

-- Show view definition
SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';

-- Update view
CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id, first_name, last_name, email, department_id, salary
FROM employees
WHERE status = 'ACTIVE';

-- Drop view
DROP VIEW emp_view;

-- Refresh materialized view
EXEC DBMS_MVIEW.REFRESH('EMP_DEPT_MV');

-- Drop materialized view
DROP MATERIALIZED VIEW emp_dept_mv;

Stored Procedures

Creating Procedures

-- Simple procedure
CREATE OR REPLACE PROCEDURE get_employee_count
IS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;
  DBMS_OUTPUT.PUT_LINE('Total employees: ' || v_count);
END;
/

-- Procedure with parameters
CREATE OR REPLACE PROCEDURE get_emp_by_dept(
  p_dept_id IN NUMBER,
  p_count OUT NUMBER
)
IS
BEGIN
  SELECT COUNT(*) INTO p_count 
  FROM employees 
  WHERE department_id = p_dept_id;
END;
/

-- Procedure with IN OUT parameter
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER,
  p_salary IN OUT NUMBER
)
IS
  v_current_salary NUMBER;
BEGIN
  SELECT salary INTO v_current_salary 
  FROM employees 
  WHERE employee_id = p_emp_id;

  UPDATE employees 
  SET salary = p_salary 
  WHERE employee_id = p_emp_id;

  p_salary := v_current_salary; -- Return old salary
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

-- Procedure with cursor
CREATE OR REPLACE PROCEDURE print_high_earners(p_min_salary IN NUMBER)
IS
  CURSOR c_employees IS
    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary >= p_min_salary
    ORDER BY salary DESC;
BEGIN
  FOR emp_rec IN c_employees LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name || 
                        ': $' || emp_rec.salary);
  END LOOP;
END;
/

Calling Procedures

-- Call simple procedure
EXEC get_employee_count;

-- Call procedure with parameters
DECLARE
  v_count NUMBER;
BEGIN
  get_emp_by_dept(10, v_count);
  DBMS_OUTPUT.PUT_LINE('Department 10 has ' || v_count || ' employees');
END;
/

-- Call procedure with IN OUT parameter
DECLARE
  v_salary NUMBER := 7000;
BEGIN
  update_salary(101, v_salary);
  DBMS_OUTPUT.PUT_LINE('Previous salary was: ' || v_salary);
END;
/

Functions

Creating Functions

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  p_emp_id IN NUMBER
) RETURN VARCHAR2
IS
  v_full_name VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name 
  INTO v_full_name
  FROM employees
  WHERE employee_id = p_emp_id;

  RETURN v_full_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

-- Function with complex logic
CREATE OR REPLACE FUNCTION calculate_bonus(
  p_emp_id IN NUMBER,
  p_bonus_pct IN NUMBER DEFAULT 0.1
) RETURN NUMBER
IS
  v_salary NUMBER;
  v_performance_rating NUMBER;
  v_bonus NUMBER;
BEGIN
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = p_emp_id;

  -- Get performance rating (assume this exists)
  SELECT rating INTO v_performance_rating
  FROM performance_reviews
  WHERE employee_id = p_emp_id
  AND review_year = EXTRACT(YEAR FROM SYSDATE);

  -- Calculate bonus based on performance
  CASE v_performance_rating
    WHEN 5 THEN v_bonus := v_salary * (p_bonus_pct * 2);
    WHEN 4 THEN v_bonus := v_salary * (p_bonus_pct * 1.5);
    WHEN 3 THEN v_bonus := v_salary * p_bonus_pct;
    ELSE v_bonus := 0;
  END CASE;

  RETURN v_bonus;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;
/

-- Pipelined table function
CREATE OR REPLACE TYPE emp_obj AS OBJECT (
  employee_id NUMBER,
  full_name VARCHAR2(100),
  salary NUMBER
);
/

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;
/

CREATE OR REPLACE FUNCTION get_employees_by_dept(p_dept_id NUMBER)
RETURN emp_tab PIPELINED
IS
  v_emp emp_obj;
BEGIN
  FOR rec IN (SELECT employee_id, first_name || ' ' || last_name AS full_name, salary
              FROM employees
              WHERE department_id = p_dept_id) LOOP
    v_emp := emp_obj(rec.employee_id, rec.full_name, rec.salary);
    PIPE ROW(v_emp);
  END LOOP;
  RETURN;
END;
/

Using Functions

-- Use function in SELECT
SELECT employee_id, get_full_name(employee_id) AS full_name
FROM employees;

-- Use function in WHERE clause
SELECT * FROM employees
WHERE calculate_bonus(employee_id) > 5000;

-- Use pipelined function
SELECT * FROM TABLE(get_employees_by_dept(10));

Packages

Creating Packages

-- Package specification
CREATE OR REPLACE PACKAGE emp_mgmt AS
  -- Public constants
  c_max_salary CONSTANT NUMBER := 50000;

  -- Public types
  TYPE emp_cursor_type IS REF CURSOR RETURN employees%ROWTYPE;

  -- Public procedures and functions
  PROCEDURE hire_employee(
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_email IN VARCHAR2,
    p_job_id IN VARCHAR2,
    p_dept_id IN NUMBER,
    p_emp_id OUT NUMBER
  );

  FUNCTION get_employee_salary(p_emp_id IN NUMBER) RETURN NUMBER;

  PROCEDURE get_dept_employees(
    p_dept_id IN NUMBER,
    p_cursor OUT emp_cursor_type
  );

END emp_mgmt;
/

-- Package body
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

  -- Private variables
  g_package_name CONSTANT VARCHAR2(30) := 'EMP_MGMT';

  -- Private procedure
  PROCEDURE log_action(p_action IN VARCHAR2) IS
  BEGIN
    INSERT INTO audit_log (package_name, action, timestamp)
    VALUES (g_package_name, p_action, SYSDATE);
  END log_action;

  -- Public procedure implementation
  PROCEDURE hire_employee(
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_email IN VARCHAR2,
    p_job_id IN VARCHAR2,
    p_dept_id IN NUMBER,
    p_emp_id OUT NUMBER
  ) IS
  BEGIN
    SELECT employees_seq.NEXTVAL INTO p_emp_id FROM dual;

    INSERT INTO employees (
      employee_id, first_name, last_name, email, 
      job_id, department_id, hire_date
    ) VALUES (
      p_emp_id, p_first_name, p_last_name, p_email,
      p_job_id, p_dept_id, SYSDATE
    );

    log_action('HIRE_EMPLOYEE: ' || p_emp_id);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
  END hire_employee;

  -- Public function implementation
  FUNCTION get_employee_salary(p_emp_id IN NUMBER) RETURN NUMBER IS
    v_salary NUMBER;
  BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    RETURN v_salary;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN NULL;
  END get_employee_salary;

  -- Public procedure with cursor
  PROCEDURE get_dept_employees(
    p_dept_id IN NUMBER,
    p_cursor OUT emp_cursor_type
  ) IS
  BEGIN
    OPEN p_cursor FOR
      SELECT * FROM employees
      WHERE department_id = p_dept_id
      ORDER BY last_name;
  END get_dept_employees;

END emp_mgmt;
/

Using Packages

-- Call package procedure
DECLARE
  v_emp_id NUMBER;
BEGIN
  emp_mgmt.hire_employee(
    p_first_name => 'John',
    p_last_name => 'Smith',
    p_email => 'john.smith@company.com',
    p_job_id => 'IT_PROG',
    p_dept_id => 60,
    p_emp_id => v_emp_id
  );
  DBMS_OUTPUT.PUT_LINE('New employee ID: ' || v_emp_id);
END;
/

-- Call package function
SELECT employee_id, emp_mgmt.get_employee_salary(employee_id) AS salary
FROM employees;

-- Use package cursor
DECLARE
  v_cursor emp_mgmt.emp_cursor_type;
  v_emp employees%ROWTYPE;
BEGIN
  emp_mgmt.get_dept_employees(60, v_cursor);

  LOOP
    FETCH v_cursor INTO v_emp;
    EXIT WHEN v_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name);
  END LOOP;

  CLOSE v_cursor;
END;
/

Triggers

Creating Triggers

-- Before insert trigger
CREATE OR REPLACE TRIGGER trg_emp_before_insert
  BEFORE INSERT ON employees
  FOR EACH ROW
BEGIN
  -- Auto-generate employee ID if not provided
  IF :NEW.employee_id IS NULL THEN
    SELECT employees_seq.NEXTVAL INTO :NEW.employee_id FROM dual;
  END IF;

  -- Set hire date to current date if not provided
  IF :NEW.hire_date IS NULL THEN
    :NEW.hire_date := SYSDATE;
  END IF;

  -- Convert email to lowercase
  :NEW.email := LOWER(:NEW.email);
END;
/

-- After update trigger
CREATE OR REPLACE TRIGGER trg_emp_after_update
  AFTER UPDATE ON employees
  FOR EACH ROW
BEGIN
  -- Log salary changes
  IF :OLD.salary != :NEW.salary THEN
    INSERT INTO salary_audit (
      employee_id, old_salary, new_salary, change_date, changed_by
    ) VALUES (
      :NEW.employee_id, :OLD.salary, :NEW.salary, SYSDATE, USER
    );
  END IF;
END;
/

-- Instead of trigger (for views)
CREATE OR REPLACE TRIGGER trg_emp_view_insert
  INSTEAD OF INSERT ON emp_dept_view
  FOR EACH ROW
BEGIN
  INSERT INTO employees (
    employee_id, first_name, last_name, email, department_id
  ) VALUES (
    :NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.email, :NEW.department_id
  );
END;
/

-- Compound trigger (Oracle 11g+)
CREATE OR REPLACE TRIGGER trg_emp_compound
  FOR INSERT OR UPDATE OR DELETE ON employees
  COMPOUND TRIGGER

  -- Declaration section
  TYPE emp_id_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  g_emp_ids emp_id_array;
  g_count PLS_INTEGER := 0;

  -- Before statement
  BEFORE STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting DML operation on employees table');
    g_count := 0;
  END BEFORE STATEMENT;

  -- Before each row
  BEFORE EACH ROW IS
  BEGIN
    IF INSERTING THEN
      g_count := g_count + 1;
      g_emp_ids(g_count) := :NEW.employee_id;
    ELSIF UPDATING THEN
      g_count := g_count + 1;
      g_emp_ids(g_count) := :NEW.employee_id;
    ELSIF DELETING THEN
      g_count := g_count + 1;
      g_emp_ids(g_count) := :OLD.employee_id;
    END IF;
  END BEFORE EACH ROW;

  -- After statement
  AFTER STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Processed ' || g_count || ' rows');

    -- Process collected employee IDs
    FOR i IN 1..g_count LOOP
      -- Update some summary table
      NULL; -- Placeholder for actual logic
    END LOOP;
  END AFTER STATEMENT;

END trg_emp_compound;
/

Managing Triggers

-- Show triggers
SELECT trigger_name, table_name, triggering_event, status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

-- Show trigger source
SELECT trigger_body FROM user_triggers WHERE trigger_name = 'TRG_EMP_BEFORE_INSERT';

-- Enable/disable trigger
ALTER TRIGGER trg_emp_before_insert DISABLE;
ALTER TRIGGER trg_emp_before_insert ENABLE;

-- Drop trigger
DROP TRIGGER trg_emp_before_insert;

Transactions

Transaction Control

-- Start transaction (implicit)
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1001, 'John', 'Doe');

-- Savepoint
SAVEPOINT sp1;

UPDATE employees SET salary = 5000 WHERE employee_id = 1001;

SAVEPOINT sp2;

DELETE FROM employees WHERE employee_id = 1002;

-- Rollback to savepoint
ROLLBACK TO sp1;

-- Commit transaction
COMMIT;

-- Rollback entire transaction
ROLLBACK;

Autonomous Transactions

-- Autonomous transaction procedure
CREATE OR REPLACE PROCEDURE log_error(
  p_error_msg IN VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (error_message, error_date)
  VALUES (p_error_msg, SYSDATE);
  COMMIT; -- This commit doesn't affect the calling transaction
END;
/

-- Using autonomous transaction
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (1001, 'John', 'Doe');

  -- This will be logged even if the main transaction rolls back
  log_error('Processing employee 1001');

  -- Simulate error
  RAISE_APPLICATION_ERROR(-20001, 'Simulated error');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- Main transaction rolls back, but log entry remains
    RAISE;
END;
/

Lock Management

-- Explicit locking
SELECT * FROM employees WHERE employee_id = 1001 FOR UPDATE;

-- Lock with NOWAIT
SELECT * FROM employees WHERE employee_id = 1001 FOR UPDATE NOWAIT;

-- Lock with timeout
SELECT * FROM employees WHERE employee_id = 1001 FOR UPDATE WAIT 5;

-- Table-level lock
LOCK TABLE employees IN EXCLUSIVE MODE;

-- Check locks
SELECT object_name, object_type, session_id, locked_mode
FROM v$locked_object lo
JOIN dba_objects do ON lo.object_id = do.object_id;

PL/SQL

Basic PL/SQL Block

-- Anonymous block
DECLARE
  v_emp_count NUMBER;
  v_avg_salary NUMBER;
BEGIN
  SELECT COUNT(*), AVG(salary)
  INTO v_emp_count, v_avg_salary
  FROM employees;

  DBMS_OUTPUT.PUT_LINE('Total employees: ' || v_emp_count);
  DBMS_OUTPUT.PUT_LINE('Average salary: ' || v_avg_salary);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Variables and Constants

DECLARE
  -- Variable declarations
  v_employee_id NUMBER(6);
  v_first_name VARCHAR2(20);
  v_hire_date DATE := SYSDATE;
  v_salary NUMBER(8,2) DEFAULT 5000;

  -- Constants
  c_max_salary CONSTANT NUMBER := 50000;
  c_company_name CONSTANT VARCHAR2(30) := 'ACME Corp';

  -- %TYPE attribute
  v_last_name employees.last_name%TYPE;

  -- %ROWTYPE attribute
  v_employee employees%ROWTYPE;

  -- Record type
  TYPE emp_record_type IS RECORD (
    emp_id NUMBER(6),
    full_name VARCHAR2(50),
    annual_salary NUMBER(10,2)
  );
  v_emp_rec emp_record_type;

  -- Collection types
  TYPE number_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_salaries number_array;

  TYPE string_list IS TABLE OF VARCHAR2(100);
  v_names string_list := string_list();

BEGIN
  -- Variable assignments
  v_employee_id := 100;
  v_first_name := 'John';

  -- Using %ROWTYPE
  SELECT * INTO v_employee FROM employees WHERE employee_id = 100;

  -- Using record
  v_emp_rec.emp_id := v_employee.employee_id;
  v_emp_rec.full_name := v_employee.first_name || ' ' || v_employee.last_name;
  v_emp_rec.annual_salary := v_employee.salary * 12;

  -- Using collections
  v_salaries(1) := 5000;
  v_salaries(2) := 6000;

  v_names.EXTEND;
  v_names(1) := 'John Doe';

  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_rec.full_name);
  DBMS_OUTPUT.PUT_LINE('Annual salary: ' || v_emp_rec.annual_salary);
END;
/

Control Structures

DECLARE
  v_grade CHAR(1) := 'B';
  v_salary NUMBER := 5000;
  v_bonus NUMBER;
  v_counter NUMBER := 1;
BEGIN
  -- IF-THEN-ELSE
  IF v_salary > 10000 THEN
    v_bonus := v_salary * 0.2;
  ELSIF v_salary > 5000 THEN
    v_bonus := v_salary * 0.1;
  ELSE
    v_bonus := v_salary * 0.05;
  END IF;

  -- CASE statement
  CASE v_grade
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('Average');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Below Average');
  END CASE;

  -- CASE expression
  v_bonus := CASE
    WHEN v_salary > 10000 THEN v_salary * 0.2
    WHEN v_salary > 5000 THEN v_salary * 0.1
    ELSE v_salary * 0.05
  END;

  -- Simple LOOP
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5;
  END LOOP;

  -- WHILE LOOP
  v_counter := 1;
  WHILE v_counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('While counter: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;

  -- FOR LOOP
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('For counter: ' || i);
  END LOOP;

  -- Reverse FOR LOOP
  FOR i IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Reverse counter: ' || i);
  END LOOP;
END;
/

Cursors

DECLARE
  -- Explicit cursor
  CURSOR c_employees IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 10;

  -- Cursor with parameters
  CURSOR c_emp_by_dept(p_dept_id NUMBER) IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id;

  -- Cursor variables
  v_emp_id NUMBER;
  v_first_name VARCHAR2(20);
  v_last_name VARCHAR2(25);
  v_salary NUMBER;

  -- Record for cursor
  v_emp_rec c_employees%ROWTYPE;

BEGIN
  -- Explicit cursor processing
  OPEN c_employees;
  LOOP
    FETCH c_employees INTO v_emp_id, v_first_name, v_last_name, v_salary;
    EXIT WHEN c_employees%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ': $' || v_salary);
  END LOOP;
  CLOSE c_employees;

  -- Cursor FOR loop (implicit open, fetch, close)
  FOR emp_rec IN c_employees LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;

  -- Parameterized cursor
  FOR emp_rec IN c_emp_by_dept(20) LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 20: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;

  -- Cursor with SELECT statement
  FOR emp_rec IN (SELECT first_name, last_name FROM employees WHERE department_id = 30) LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 30: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;

  -- REF CURSOR
  DECLARE
    TYPE emp_cursor_type IS REF CURSOR;
    v_emp_cursor emp_cursor_type;
    v_sql VARCHAR2(1000);
  BEGIN
    v_sql := 'SELECT first_name, last_name FROM employees WHERE department_id = 40';
    OPEN v_emp_cursor FOR v_sql;

    LOOP
      FETCH v_emp_cursor INTO v_first_name, v_last_name;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Dept 40: ' || v_first_name || ' ' || v_last_name);
    END LOOP;

    CLOSE v_emp_cursor;
  END;
END;
/

Exception Handling

DECLARE
  v_emp_id NUMBER := 999;
  v_first_name VARCHAR2(20);
  v_salary NUMBER;

  -- User-defined exception
  salary_too_high EXCEPTION;
  PRAGMA EXCEPTION_INIT(salary_too_high, -20001);

BEGIN
  -- This will raise NO_DATA_FOUND
  SELECT first_name, salary 
  INTO v_first_name, v_salary
  FROM employees 
  WHERE employee_id = v_emp_id;

  -- Check business rule
  IF v_salary > 50000 THEN
    RAISE salary_too_high;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ' not found');

  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple employees found');

  WHEN salary_too_high THEN
    DBMS_OUTPUT.PUT_LINE('Salary exceeds maximum allowed');

  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
    -- Re-raise the exception
    RAISE;
END;
/

Backup and Recovery

RMAN Backup

# Connect to RMAN
rman target /

# Show configuration
SHOW ALL;

# Configure backup settings
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

# Full database backup
BACKUP DATABASE;

# Incremental backup
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;

# Backup specific tablespace
BACKUP TABLESPACE users;

# Backup archive logs
BACKUP ARCHIVELOG ALL;

# Backup with compression
BACKUP AS COMPRESSED BACKUPSET DATABASE;

# List backups
LIST BACKUP;
LIST BACKUP SUMMARY;

# Delete obsolete backups
DELETE OBSOLETE;

# Crosscheck backups
CROSSCHECK BACKUP;

Export/Import (Data Pump)

# Full database export
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log

# Schema export
expdp hr/password SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp LOGFILE=hr_schema.log

# Table export
expdp hr/password TABLES=employees,departments DIRECTORY=dpump_dir DUMPFILE=hr_tables.dmp

# Export with query
expdp hr/password TABLES=employees QUERY=employees:"WHERE hire_date > DATE '2020-01-01'" DIRECTORY=dpump_dir DUMPFILE=recent_employees.dmp

# Parallel export
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db_%U.dmp PARALLEL=4

# Full database import
impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=import.log

# Schema import
impdp system/password SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp REMAP_SCHEMA=hr:hr_new

# Table import
impdp hr/password TABLES=employees DIRECTORY=dpump_dir DUMPFILE=hr_tables.dmp TABLE_EXISTS_ACTION=REPLACE

# Import with tablespace remapping
impdp system/password SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp REMAP_TABLESPACE=users:users_new

Point-in-Time Recovery

# RMAN point-in-time recovery
rman target /

# Restore and recover to specific time
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE UNTIL TIME "TO_DATE('2023-12-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
RECOVER DATABASE UNTIL TIME "TO_DATE('2023-12-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
ALTER DATABASE OPEN RESETLOGS;

# Restore and recover to specific SCN
RESTORE DATABASE UNTIL SCN 1234567;
RECOVER DATABASE UNTIL SCN 1234567;

# Tablespace point-in-time recovery
RECOVER TABLESPACE users UNTIL TIME "TO_DATE('2023-12-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";

Performance Tuning

SQL Tuning

-- Explain plan
EXPLAIN PLAN FOR
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Autotrace
SET AUTOTRACE ON;
SELECT * FROM employees WHERE salary > 5000;

-- SQL Trace
ALTER SESSION SET SQL_TRACE = TRUE;
-- Run your SQL statements
ALTER SESSION SET SQL_TRACE = FALSE;

-- 10046 trace
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Run your SQL statements
ALTER SESSION SET EVENTS '10046 trace name context off';

-- AWR report
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- SQL Tuning Advisor
DECLARE
  task_name VARCHAR2(30);
  sql_text CLOB;
BEGIN
  sql_text := 'SELECT * FROM employees WHERE salary > 5000';

  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => sql_text,
    task_name => 'tune_employees_query'
  );

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;
/

-- View tuning recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_employees_query') FROM dual;

Statistics Management

-- Gather table statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- Gather schema statistics
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- Gather database statistics
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- Gather statistics with options
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  degree => DBMS_STATS.AUTO_DEGREE,
  cascade => TRUE
);

-- Delete statistics
EXEC DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES');

-- Lock statistics
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- Unlock statistics
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- View statistics
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables;

SELECT column_name, num_distinct, density, num_nulls, histogram
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';

Memory Management

-- Show SGA components
SELECT component, current_size/1024/1024 AS current_mb, 
       max_size/1024/1024 AS max_mb
FROM v$sga_dynamic_components;

-- Show PGA usage
SELECT name, value/1024/1024 AS mb FROM v$pgastat;

-- Buffer cache hit ratio
SELECT name, value FROM v$sysstat WHERE name = 'db block gets';
SELECT name, value FROM v$sysstat WHERE name = 'consistent gets';
SELECT name, value FROM v$sysstat WHERE name = 'physical reads';

-- Library cache hit ratio
SELECT namespace, gethitratio FROM v$librarycache;

-- Show top SQL by buffer gets
SELECT sql_id, buffer_gets, executions, 
       buffer_gets/executions AS gets_per_exec
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;

Monitoring

Database Monitoring

-- Database status
SELECT name, open_mode, database_role FROM v$database;

-- Instance information
SELECT instance_name, host_name, version, status, startup_time
FROM v$instance;

-- Session information
SELECT sid, serial#, username, program, machine, status
FROM v$session
WHERE username IS NOT NULL;

-- Active sessions
SELECT COUNT(*) AS active_sessions
FROM v$session
WHERE status = 'ACTIVE';

-- Blocking sessions
SELECT blocking_session, sid, serial#, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;

-- Tablespace usage
SELECT tablespace_name,
       ROUND(used_mb, 2) AS used_mb,
       ROUND(free_mb, 2) AS free_mb,
       ROUND(total_mb, 2) AS total_mb,
       ROUND((used_mb/total_mb)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 AS total_mb,
         SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END)/1024/1024 AS max_mb
  FROM dba_data_files
  GROUP BY tablespace_name
) df
JOIN (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 AS free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
CROSS APPLY (
  SELECT df.total_mb - fs.free_mb AS used_mb FROM dual
);

-- Archive log generation
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,
       COUNT(*) AS log_count,
       SUM(blocks * block_size)/1024/1024 AS mb_generated
FROM v$archived_log
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Performance Monitoring

-- Top wait events
SELECT event, total_waits, time_waited_micro/1000000 AS time_waited_sec
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

-- Top SQL by elapsed time
SELECT sql_id, elapsed_time/1000000 AS elapsed_sec, executions,
       elapsed_time/executions/1000000 AS avg_elapsed_sec
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- I/O statistics
SELECT name, phyrds, phywrts, readtim, writetim
FROM v$filestat f
JOIN v$datafile d ON f.file# = d.file#
ORDER BY phyrds + phywrts DESC;

-- Redo log switches
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,
       COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Security

User Security

-- Password policy
CREATE PROFILE secure_profile LIMIT
  SESSIONS_PER_USER 2
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL 3000
  CONNECT_TIME 45
  IDLE_TIME 10
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL 1000
  PRIVATE_SGA 15K
  COMPOSITE_LIMIT 5000000
  PASSWORD_LIFE_TIME 60
  PASSWORD_GRACE_TIME 10
  PASSWORD_REUSE_TIME 1800
  PASSWORD_REUSE_MAX 10
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_VERIFY_FUNCTION verify_function;

-- Apply profile to user
ALTER USER app_user PROFILE secure_profile;

-- Audit settings
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;
AUDIT CREATE SESSION BY app_user BY ACCESS;

-- Enable unified auditing (Oracle 12c+)
AUDIT POLICY ora_database_parameter;
AUDIT POLICY ora_secureconfig;

-- View audit records
SELECT username, action_name, object_name, timestamp
FROM dba_audit_trail
WHERE username = 'APP_USER'
ORDER BY timestamp DESC;

Encryption

-- Transparent Data Encryption (TDE)
-- Create encrypted tablespace
CREATE TABLESPACE secure_data
DATAFILE '/u01/app/oracle/oradata/mydb/secure_data01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

-- Create table with encrypted column
CREATE TABLE sensitive_data (
  id NUMBER,
  ssn VARCHAR2(11) ENCRYPT USING 'AES256',
  credit_card VARCHAR2(16) ENCRYPT USING 'AES256',
  name VARCHAR2(100)
) TABLESPACE secure_data;

-- Network encryption (sqlnet.ora)
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA1)

Virtual Private Database (VPD)

-- Create security policy function
CREATE OR REPLACE FUNCTION emp_security_policy(
  schema_var IN VARCHAR2,
  table_var IN VARCHAR2
) RETURN VARCHAR2
IS
  v_predicate VARCHAR2(400);
BEGIN
  IF USER = 'HR_MANAGER' THEN
    v_predicate := '1=1'; -- See all records
  ELSE
    v_predicate := 'employee_id = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
  END IF;

  RETURN v_predicate;
END;
/

-- Apply security policy
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema => 'HR',
    object_name => 'EMPLOYEES',
    policy_name => 'EMP_SECURITY_POLICY',
    function_schema => 'HR',
    policy_function => 'EMP_SECURITY_POLICY',
    statement_types => 'SELECT, INSERT, UPDATE, DELETE'
  );
END;
/

Best Practices

Schema Design Best Practices

-- Use appropriate data types
CREATE TABLE orders (
  order_id NUMBER(10) PRIMARY KEY,           -- Use NUMBER for IDs
  order_date DATE NOT NULL,                  -- Use DATE for dates
  amount NUMBER(10,2) NOT NULL,              -- Use NUMBER(p,s) for money
  status VARCHAR2(20) DEFAULT 'PENDING',     -- Use VARCHAR2 for strings
  customer_id NUMBER(10) NOT NULL,
  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Use constraints for data integrity
ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0);
ALTER TABLE orders ADD CONSTRAINT chk_status CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED'));

-- Use sequences for primary keys
CREATE SEQUENCE orders_seq START WITH 1 INCREMENT BY 1 CACHE 20;

-- Use indexes appropriately
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

Performance Best Practices

-- Use bind variables
-- Bad: SELECT * FROM employees WHERE employee_id = 100;
-- Good: SELECT * FROM employees WHERE employee_id = :emp_id;

-- Use appropriate hints when necessary
SELECT /*+ INDEX(e idx_emp_dept_id) */ *
FROM employees e
WHERE department_id = 10;

-- Avoid SELECT *
-- Bad: SELECT * FROM employees;
-- Good: SELECT employee_id, first_name, last_name FROM employees;

-- Use EXISTS instead of IN for subqueries
-- Good: 
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);

-- Use ROWNUM for limiting results
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

Security Best Practices

-- Use least privilege principle
-- Create role for specific access
CREATE ROLE app_read_role;
GRANT SELECT ON hr.employees TO app_read_role;
GRANT SELECT ON hr.departments TO app_read_role;
GRANT app_read_role TO app_user;

-- Use strong authentication
-- Enable password complexity
ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

-- Regular maintenance
-- Gather statistics regularly
EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

-- Monitor and tune
-- Regular AWR reports
-- Monitor alert log
-- Check tablespace usage
-- Review slow queries

---

Summary

Oracle Database is a comprehensive enterprise database management system that provides robust features for data management, security, performance, and scalability. This cheatsheet covers essential Oracle Database operations from basic administration to advanced features.

Key Strengths: - Enterprise Features: Advanced security, partitioning, compression, and high availability - Performance: Sophisticated query optimizer and extensive tuning capabilities - Scalability: Support for very large databases and high-concurrency workloads - PL/SQL: Powerful procedural language for complex business logic - Reliability: ACID compliance with advanced backup and recovery options

Best Use Cases: - Large enterprise applications - Data warehousing and analytics - Mission-critical systems requiring high availability - Applications with complex business logic - Systems requiring advanced security features

Important Considerations: - Licensing costs can be significant - Requires skilled database administrators - Complex installation and configuration - Resource-intensive (memory and CPU) - Regular maintenance and monitoring are essential

By following the practices and techniques outlined in this cheatsheet, you can effectively design, implement, and maintain Oracle Database systems that meet enterprise requirements for performance, security, and reliability.