Developer's Catalog

Database

Oracle Database

Overview

Oracle Database is the world's leading relational database management system designed for enterprise use. It provides high reliability, scalability, and security features, optimized for mission-critical applications. It is widely adopted in finance, government, and large enterprises as an industry-standard database.

Details

Oracle Database development began in 1979 by Larry Ellison and others. Through over 40 years of continuous evolution, it has become a comprehensive database platform with the following characteristics:

Key Features

• Autonomous Database: Automated management, patching, and tuning
• In-Memory Computing: Ultra-fast query execution through in-memory processing
• Multitenant Architecture: Container Database (CDB) and Pluggable Database (PDB)
• Advanced Security: Transparent data encryption, data masking, audit capabilities
• Real Application Clusters (RAC): Active-active clustering
• Advanced Analytics: Machine learning, graph analytics, spatial data processing
• JSON and XML: Complete NoSQL data support
• Blockchain Tables: Tables with tamper-evident capabilities

Architecture

• System Global Area (SGA): Shared memory area
• Process Global Area (PGA): Process-specific memory area
• Background Processes: PMON, SMON, DBWR, LGWR, etc.
• Oracle Net Services: Network communication and security
• Automatic Storage Management (ASM): Automated storage management

Edition Structure

• Oracle Database Free: Free version for development and learning
• Standard Edition: For small to medium enterprises
• Enterprise Edition: High-functionality version for enterprises
• Autonomous Database: Cloud-optimized version

Advantages and Disadvantages

Advantages

• Highest Level of Reliability: Enterprise-grade availability and data consistency
• Excellent Performance: High-speed processing with In-Memory technology
• Comprehensive Features: Advanced features including analytics, AI/ML, blockchain
• Strong Security: Multi-layered defense and compliance capabilities
• Rich Tools: Oracle SQL Developer, Enterprise Manager, etc.
• Mature Ecosystem: Abundant third-party tools and vendor support
• Expert Support: 24/7 enterprise support from Oracle

Disadvantages

• High Cost: Expensive licensing and maintenance fees
• Complex Management: Requires specialized knowledge for configuration and tuning
• Vendor Lock-in: Risk of dependency on Oracle-specific features
• Resource Consumption: Requires large amounts of memory and storage
• High Learning Cost: Requires time and investment to master
• License Complexity: Complex licensing structure and audit risks

Key Links

• Official Website
• Oracle Database Documentation
• Oracle Live SQL
• Oracle Learning Library

Code Examples

Installation & Setup

# Oracle Database Free (Linux)
dnf install oracle-database-free-23c
/etc/init.d/oracle-db-free-23c configure

# Docker environment
docker pull container-registry.oracle.com/database/free:latest
docker run --name oracle-free \
  -p 1521:1521 -p 5500:5500 \
  -e ORACLE_PWD=mypassword \
  container-registry.oracle.com/database/free:latest

# Environment variable setup
export ORACLE_HOME=/opt/oracle/product/23c/dbhome_1
export ORACLE_SID=FREE
export PATH=$ORACLE_HOME/bin:$PATH

# Connection with SQLPlus
sqlplus sys/password@localhost:1521/FREE as sysdba

Basic Operations (CRUD)

-- Create tablespace and user
CREATE TABLESPACE company_data
DATAFILE 'company_data.dbf' SIZE 100M AUTOEXTEND ON;

CREATE USER company_user IDENTIFIED BY strongpassword
DEFAULT TABLESPACE company_data;

GRANT CONNECT, RESOURCE TO company_user;

-- Connect and create table
CONNECT company_user/strongpassword@localhost:1521/FREE;

CREATE TABLE employees (
    employee_id NUMBER(10) PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2),
    department_id NUMBER(10)
);

-- Create sequence
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

-- Insert data
INSERT INTO employees VALUES (
    emp_seq.NEXTVAL, 'John', 'Doe', '[email protected]', 
    SYSDATE, 75000, 100
);

-- Select data
SELECT employee_id, first_name || ' ' || last_name AS full_name,
       email, hire_date, salary
FROM employees
WHERE department_id = 100;

-- Update data
UPDATE employees 
SET salary = salary * 1.1 
WHERE hire_date < ADD_MONTHS(SYSDATE, -12);

COMMIT;

Data Modeling

-- Table design with foreign key constraints
CREATE TABLE departments (
    department_id NUMBER(10) PRIMARY KEY,
    department_name VARCHAR2(100) NOT NULL,
    manager_id NUMBER(10),
    location_id NUMBER(10)
);

CREATE TABLE employees (
    employee_id NUMBER(10) PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    department_id NUMBER(10),
    CONSTRAINT fk_emp_dept 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

-- JSON support
CREATE TABLE customer_data (
    customer_id NUMBER PRIMARY KEY,
    customer_info JSON,
    preferences JSON
);

INSERT INTO customer_data VALUES (
    1,
    JSON_OBJECT('name' VALUE 'John Doe', 'age' VALUE 35, 'city' VALUE 'Tokyo'),
    JSON_OBJECT('newsletter' VALUE true, 'language' VALUE 'en')
);

-- Partitioning
CREATE TABLE sales_data (
    sale_id NUMBER,
    sale_date DATE,
    customer_id NUMBER,
    amount NUMBER(10,2)
) PARTITION BY RANGE (sale_date) (
    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)
);

Indexing & Optimization

-- Create indexes
CREATE INDEX idx_emp_department ON employees(department_id);
CREATE INDEX idx_emp_name ON employees(last_name, first_name);

-- Function-based index
CREATE INDEX idx_emp_email_upper ON employees(UPPER(email));

-- Bitmap index
CREATE BITMAP INDEX idx_emp_status ON employees(status);

-- Check execution plan
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Update statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('COMPANY_USER', 'EMPLOYEES');

-- Performance monitoring
SELECT sql_text, executions, elapsed_time, cpu_time
FROM v$sql
WHERE executions > 10
ORDER BY elapsed_time DESC;

Practical Examples

-- PL/SQL Stored Procedure
CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_department_id IN NUMBER,
    p_bonus_rate IN NUMBER DEFAULT 0.1
) AS
    v_total_bonus NUMBER := 0;
BEGIN
    FOR emp_rec IN (SELECT employee_id, salary FROM employees 
                    WHERE department_id = p_department_id) LOOP
        UPDATE employees 
        SET bonus = salary * p_bonus_rate
        WHERE employee_id = emp_rec.employee_id;
        
        v_total_bonus := v_total_bonus + (emp_rec.salary * p_bonus_rate);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total bonus allocated: ' || v_total_bonus);
    COMMIT;
END;
/

-- PL/SQL Function
CREATE OR REPLACE FUNCTION get_employee_age(p_birth_date DATE)
RETURN NUMBER AS
BEGIN
    RETURN MONTHS_BETWEEN(SYSDATE, p_birth_date) / 12;
END;
/

-- Trigger
CREATE OR REPLACE TRIGGER audit_employee_changes
    AFTER UPDATE ON employees
    FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (
        employee_id, change_date, old_salary, new_salary, changed_by
    ) VALUES (
        :NEW.employee_id, SYSDATE, :OLD.salary, :NEW.salary, USER
    );
END;
/

-- Advanced Queuing (Message Queue)
BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE('order_queue_table', 'SYS.AQ$_JMS_TEXT_MESSAGE');
    DBMS_AQADM.CREATE_QUEUE('order_queue', 'order_queue_table');
    DBMS_AQADM.START_QUEUE('order_queue');
END;
/

Best Practices

-- Database Link
CREATE DATABASE LINK remote_db
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_host:1521/remote_service';

-- Materialized View
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FAST ON COMMIT AS
SELECT department_id, SUM(salary) as total_salary, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

-- Security settings
-- Transparent Data Encryption
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "myencryptionkey";
ALTER TABLE sensitive_data MODIFY (ssn ENCRYPT);

-- Virtual Private Database (VPD)
CREATE OR REPLACE FUNCTION employee_security_policy(
    schema_var IN VARCHAR2,
    table_var IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
    IF USER = 'HR_MANAGER' THEN
        RETURN '1=1';  -- Full access
    ELSE
        RETURN 'department_id = SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'')';
    END IF;
END;
/

-- RAC connection
-- tnsnames.ora configuration example
ORCL_RAC = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )