Oracle Database

High-performance database for enterprise. Oracle 23ai introduces full-scale AI Vector Search functionality. Strengthens AI integration with 300+ new features.

databaseRDBMSenterpriseSQLPL/SQLcommercial-database

Database Server

Oracle Database

Overview

Oracle Database is the world's most widely used enterprise relational database management system (RDBMS). It provides high availability, performance, and scalability, optimized for managing data in mission-critical applications. Since the release of its first version in 1979, it has continuously evolved, with the latest versions featuring innovative capabilities such as autonomous database functionality and multitenant architecture.

Details

Oracle Database is a comprehensive data management platform designed to meet enterprise-level requirements. While compliant with SQL standards, it provides proprietary extensions and the PL/SQL programming language, enabling the implementation of complex business logic at the database layer.

Key features include high availability through Real Application Clusters (RAC), efficient management of large-scale data through Partitioning, and robust security features through Advanced Security. Additionally, In-Memory capabilities deliver excellent performance for both OLTP and OLAP workloads.

Oracle Database can be deployed on-premises, in the cloud, or in hybrid environments. On Oracle Cloud Infrastructure, it is offered as an Autonomous Database, automating management tasks such as auto-tuning, auto-patching, and auto-backup.

Available editions include Enterprise Edition, Standard Edition 2, and Express Edition (free version), allowing organizations to choose based on their scale and requirements. The licensing model primarily consists of processor licenses and Named User Plus licenses, with additional options and packs required depending on the features used.

Pros and Cons

Pros

High reliability and availability: Enterprise-level availability features including RAC clustering and Data Guard for disaster recovery
Excellent performance: Fast data processing through In-Memory capabilities, partitioning, and parallel processing
Comprehensive security: Multi-layered security features including encryption, auditing, access control, and data masking
Scalability: Supports both vertical and horizontal scaling, capable of handling petabyte-scale data
Rich management tools: Comprehensive management and monitoring tools including Enterprise Manager, SQL Developer, and AWR/ADDM
Powerful SQL extensions: Advanced data processing capabilities including PL/SQL, analytical functions, and JSON support
Multitenant: Efficient resource management and application isolation through container databases
Long-term support: Stable operations through extended support and patch delivery

Cons

High licensing costs: Enterprise features are very expensive, with additional costs for options and packs
Complex licensing structure: Complex compliance management due to processor, user, and feature-based licensing
Steep learning curve: Specialized knowledge and experience required to leverage advanced features
High resource requirements: Requires significant memory and CPU resources with essential tuning
Vendor lock-in: Dependency on Oracle-specific features makes migration to other databases difficult
Complex initial setup: Installation and initial configuration are complex, requiring expertise for optimal setup
License audit risk: Risk of additional costs due to Oracle's strict license audits

Reference Pages

Code Examples

Basic Connection and Query

-- Connect using SQL*Plus or your client tool
CONNECT username/password@hostname:port/service_name

-- Simple SELECT statement
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 10
ORDER BY salary DESC;

-- Create a table
CREATE TABLE products (
    product_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_name  VARCHAR2(100) NOT NULL,
    price         NUMBER(10,2) CHECK (price > 0),
    created_date  DATE DEFAULT SYSDATE
);

PL/SQL Stored Procedure

-- Create a stored procedure
CREATE OR REPLACE PROCEDURE update_employee_salary (
    p_employee_id  IN  NUMBER,
    p_percentage   IN  NUMBER,
    p_new_salary   OUT NUMBER
) AS
    v_current_salary NUMBER;
BEGIN
    -- Get current salary
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE employee_id = p_employee_id
    FOR UPDATE;
    
    -- Calculate new salary
    p_new_salary := v_current_salary * (1 + p_percentage / 100);
    
    -- Update salary
    UPDATE employees
    SET salary = p_new_salary,
        last_update_date = SYSDATE
    WHERE employee_id = p_employee_id;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Salary updated successfully.');
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END update_employee_salary;
/

-- Execute the procedure
DECLARE
    v_new_salary NUMBER;
BEGIN
    update_employee_salary(100, 10, v_new_salary);
    DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new_salary);
END;
/

Partitioning

-- Create a range partitioned table
CREATE TABLE sales (
    sale_id      NUMBER,
    sale_date    DATE,
    product_id   NUMBER,
    quantity     NUMBER,
    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'),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

-- Add a partition
ALTER TABLE sales 
ADD PARTITION sales_2024_q1 VALUES LESS THAN (DATE '2024-04-01');

-- Partition maintenance
ALTER TABLE sales DROP PARTITION sales_2023_q1;

JSON Data Operations

-- Create table with JSON data
CREATE TABLE customer_orders (
    order_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_date  DATE,
    order_data  JSON
);

-- Insert JSON data
INSERT INTO customer_orders (order_date, order_data)
VALUES (SYSDATE, JSON('{
    "customer": {
        "id": 12345,
        "name": "John Doe",
        "email": "[email protected]"
    },
    "items": [
        {"product_id": 1, "quantity": 2, "price": 29.99},
        {"product_id": 2, "quantity": 1, "price": 49.99}
    ],
    "total": 109.97
}'));

-- Query JSON data
SELECT o.order_id, 
       o.order_data.customer.name AS customer_name,
       o.order_data.total AS total_amount
FROM customer_orders o
WHERE o.order_data.customer.id = 12345;

-- Detailed analysis using JSON_TABLE
SELECT o.order_id, j.*
FROM customer_orders o,
     JSON_TABLE(o.order_data, '$.items[*]'
         COLUMNS (
             product_id NUMBER PATH '$.product_id',
             quantity   NUMBER PATH '$.quantity',
             price      NUMBER PATH '$.price'
         )
     ) j
WHERE o.order_date >= DATE '2024-01-01';

Performance Tuning

-- Get execution plan
EXPLAIN PLAN FOR
SELECT /*+ INDEX(e emp_dept_idx) */ 
       e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Generate AWR report (requires Diagnostics Pack license)
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

-- Use SQL Tuning Advisor (requires Tuning Pack license)
DECLARE
    l_sql_tune_task_id VARCHAR2(100);
BEGIN
    l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_text => 'SELECT * FROM employees WHERE salary > 50000',
        user_name => USER,
        scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit => 300,
        task_name => 'emp_sal_tuning_task'
    );
    
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'emp_sal_tuning_task');
END;
/

-- Check tuning recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('emp_sal_tuning_task') FROM DUAL;

Security Features

-- Enable Transparent Data Encryption (TDE)
-- (Requires Advanced Security option license)
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "encryption_password";
ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

-- Create Virtual Private Database (VPD) policy
CREATE OR REPLACE FUNCTION emp_security_policy(
    p_schema VARCHAR2,
    p_object VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
    -- Each user can only access their department's data
    RETURN 'department_id = SYS_CONTEXT(''USERENV'', ''CLIENT_INFO'')';
END;
/

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

-- Enable auditing
AUDIT SELECT, INSERT, UPDATE, DELETE ON employees BY ACCESS;
AUDIT CREATE SESSION;