Online UUID Generator

Appearance

Generate UUID in Oracle ​

Oracle provides built-in UUID generation through the SYS_GUID() function, which generates a 16-byte globally unique identifier:

sql
-- Generate UUID using SYS_GUID()
SELECT SYS_GUID() FROM DUAL;
-- Result: 550E8400E29B41D4A716446655440000 (32 hex characters)

-- Generate formatted UUID (with hyphens)
SELECT REGEXP_REPLACE(
    RAWTOHEX(SYS_GUID()),
    '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})',
    '\1-\2-\3-\4-\5'
) AS formatted_uuid FROM DUAL;
-- Result: 550E8400-E29B-41D4-A716-446655440000

Oracle's SYS_GUID() generates globally unique identifiers with the following characteristics:

sql
-- SYS_GUID() returns RAW(16) - 16 bytes of binary data
SELECT SYS_GUID() AS raw_uuid FROM DUAL;

-- Convert to hexadecimal string
SELECT RAWTOHEX(SYS_GUID()) AS hex_uuid FROM DUAL;

-- Convert to standard UUID format with hyphens
SELECT LOWER(REGEXP_REPLACE(
    RAWTOHEX(SYS_GUID()),
    '([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})',
    '\1-\2-\3-\4-\5'
)) AS standard_uuid FROM DUAL;

-- Multiple UUIDs
SELECT SYS_GUID() AS uuid1, SYS_GUID() AS uuid2, SYS_GUID() AS uuid3 FROM DUAL;

Table Schema with UUID Primary Keys ​

Using RAW(16) for optimal storage:

sql
-- Create table with RAW UUID primary key
CREATE TABLE users (
    id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    email VARCHAR2(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data (UUID generated automatically)
INSERT INTO users (name, email) 
VALUES ('John Doe', '[email protected]');

-- Query with UUID (using HEXTORAW for string input)
SELECT * FROM users 
WHERE id = HEXTORAW('550E8400E29B41D4A716446655440000');

Using VARCHAR2 for human-readable UUIDs:

sql
-- Create table with formatted UUID
CREATE TABLE orders (
    id VARCHAR2(36) DEFAULT LOWER(REGEXP_REPLACE(
        RAWTOHEX(SYS_GUID()),
        '([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})',
        '\1-\2-\3-\4-\5'
    )) PRIMARY KEY,
    user_id RAW(16) NOT NULL,
    order_number VARCHAR2(50),
    total NUMBER(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Advanced Oracle UUID Usage ​

UUID utility functions:

sql
-- Create package for UUID utilities
CREATE OR REPLACE PACKAGE uuid_utils AS
    FUNCTION generate_uuid RETURN VARCHAR2;
    FUNCTION generate_raw_uuid RETURN RAW;
    FUNCTION format_uuid(p_raw_uuid RAW) RETURN VARCHAR2;
    FUNCTION parse_uuid(p_uuid_string VARCHAR2) RETURN RAW;
    FUNCTION is_valid_uuid(p_uuid_string VARCHAR2) RETURN NUMBER;
END uuid_utils;
/

CREATE OR REPLACE PACKAGE BODY uuid_utils AS
    
    FUNCTION generate_uuid RETURN VARCHAR2 IS
    BEGIN
        RETURN LOWER(REGEXP_REPLACE(
            RAWTOHEX(SYS_GUID()),
            '([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})',
            '\1-\2-\3-\4-\5'
        ));
    END generate_uuid;
    
    FUNCTION generate_raw_uuid RETURN RAW IS
    BEGIN
        RETURN SYS_GUID();
    END generate_raw_uuid;
    
    FUNCTION format_uuid(p_raw_uuid RAW) RETURN VARCHAR2 IS
    BEGIN
        RETURN LOWER(REGEXP_REPLACE(
            RAWTOHEX(p_raw_uuid),
            '([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})',
            '\1-\2-\3-\4-\5'
        ));
    END format_uuid;
    
    FUNCTION parse_uuid(p_uuid_string VARCHAR2) RETURN RAW IS
    BEGIN
        RETURN HEXTORAW(REPLACE(UPPER(p_uuid_string), '-', ''));
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;
    END parse_uuid;
    
    FUNCTION is_valid_uuid(p_uuid_string VARCHAR2) RETURN NUMBER IS
    BEGIN
        IF REGEXP_LIKE(p_uuid_string, '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$') THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END is_valid_uuid;
    
END uuid_utils;
/

-- Usage examples
SELECT uuid_utils.generate_uuid() AS formatted_uuid FROM DUAL;
SELECT uuid_utils.generate_raw_uuid() AS raw_uuid FROM DUAL;
SELECT uuid_utils.is_valid_uuid('550e8400-e29b-41d4-a716-446655440000') AS is_valid FROM DUAL;

Triggers for UUID generation:

sql
-- Trigger to automatically generate UUIDs
CREATE OR REPLACE TRIGGER trg_users_uuid
    BEFORE INSERT ON users
    FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := SYS_GUID();
    END IF;
END;
/

-- Trigger with formatted UUID
CREATE OR REPLACE TRIGGER trg_orders_uuid
    BEFORE INSERT ON orders
    FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := uuid_utils.generate_uuid();
    END IF;
END;
/

Performance Optimization ​

Index strategies for UUID columns:

sql
-- B-tree index on RAW UUID (default)
CREATE INDEX idx_users_id ON users(id);

-- Function-based index for formatted lookups
CREATE INDEX idx_users_formatted_id ON users(uuid_utils.format_uuid(id));

-- Composite indexes with UUID
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Reverse key index for better distribution (if needed)
CREATE INDEX idx_users_id_reverse ON users(id) REVERSE;

Partitioning with UUIDs:

sql
-- Hash partitioning with UUID
CREATE TABLE large_dataset (
    id RAW(16) DEFAULT SYS_GUID(),
    data CLOB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY HASH(id) PARTITIONS 8;

-- Range partitioning by date (more common)
CREATE TABLE time_series_data (
    id RAW(16) DEFAULT SYS_GUID(),
    event_data CLOB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE(created_at) (
    PARTITION p2024_q1 VALUES LESS THAN (DATE '2024-04-01'),
    PARTITION p2024_q2 VALUES LESS THAN (DATE '2024-07-01'),
    PARTITION p2024_q3 VALUES LESS THAN (DATE '2024-10-01'),
    PARTITION p2024_q4 VALUES LESS THAN (DATE '2025-01-01')
);

PL/SQL Integration ​

Stored procedures with UUIDs:

sql
-- Procedure to create user with UUID
CREATE OR REPLACE PROCEDURE create_user(
    p_name IN VARCHAR2,
    p_email IN VARCHAR2,
    p_user_id OUT RAW
) AS
BEGIN
    p_user_id := SYS_GUID();
    
    INSERT INTO users (id, name, email)
    VALUES (p_user_id, p_name, p_email);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END create_user;
/

-- Function to get formatted UUID
CREATE OR REPLACE FUNCTION get_user_uuid(p_user_id RAW) 
RETURN VARCHAR2 AS
BEGIN
    RETURN uuid_utils.format_uuid(p_user_id);
END get_user_uuid;
/

-- Usage
DECLARE
    v_user_id RAW(16);
    v_formatted_id VARCHAR2(36);
BEGIN
    create_user('Jane Doe', '[email protected]', v_user_id);
    v_formatted_id := get_user_uuid(v_user_id);
    DBMS_OUTPUT.PUT_LINE('Created user with UUID: ' || v_formatted_id);
END;
/

Bulk operations with UUIDs:

sql
-- Bulk insert with UUIDs
DECLARE
    TYPE t_names IS TABLE OF VARCHAR2(100);
    TYPE t_emails IS TABLE OF VARCHAR2(255);
    TYPE t_ids IS TABLE OF RAW(16);
    
    v_names t_names := t_names('User1', 'User2', 'User3', 'User4', 'User5');
    v_emails t_emails := t_emails('[email protected]', '[email protected]', 
                                  '[email protected]', '[email protected]', '[email protected]');
    v_ids t_ids := t_ids();
BEGIN
    -- Generate UUIDs
    v_ids.EXTEND(v_names.COUNT);
    FOR i IN 1..v_names.COUNT LOOP
        v_ids(i) := SYS_GUID();
    END LOOP;
    
    -- Bulk insert
    FORALL i IN 1..v_names.COUNT
        INSERT INTO users (id, name, email)
        VALUES (v_ids(i), v_names(i), v_emails(i));
    
    COMMIT;
    
    -- Display generated UUIDs
    FOR i IN 1..v_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('User ' || v_names(i) || ': ' || uuid_utils.format_uuid(v_ids(i)));
    END LOOP;
END;
/

Oracle RAC and Distributed Systems ​

UUID considerations for Oracle RAC:

sql
-- UUIDs are perfect for Oracle RAC (no sequence conflicts)
CREATE TABLE rac_safe_table (
    id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    node_id NUMBER DEFAULT SYS_CONTEXT('USERENV', 'INSTANCE'),
    data VARCHAR2(4000),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Query to see distribution across RAC nodes
SELECT node_id, COUNT(*) as record_count
FROM rac_safe_table
GROUP BY node_id
ORDER BY node_id;

Data Guard and replication:

sql
-- UUIDs work seamlessly with Data Guard
-- No special configuration needed for UUID columns

-- Materialized view with UUIDs
CREATE MATERIALIZED VIEW user_summary
REFRESH FAST ON COMMIT AS
SELECT 
    id,
    uuid_utils.format_uuid(id) as formatted_id,
    name,
    email,
    created_at
FROM users;

Migration and Data Import ​

Converting existing tables to UUID:

sql
-- Add UUID column to existing table
ALTER TABLE existing_table ADD (uuid_id RAW(16));

-- Update existing rows with UUIDs
UPDATE existing_table SET uuid_id = SYS_GUID() WHERE uuid_id IS NULL;

-- Make UUID column NOT NULL
ALTER TABLE existing_table MODIFY (uuid_id RAW(16) NOT NULL);

-- Add unique constraint
ALTER TABLE existing_table ADD CONSTRAINT uk_existing_table_uuid UNIQUE (uuid_id);

Data import with UUIDs:

sql
-- External table for CSV import with UUID generation
CREATE TABLE ext_users_csv (
    name VARCHAR2(100),
    email VARCHAR2(255)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
    )
    LOCATION ('users.csv')
);

-- Import with UUID generation
INSERT INTO users (id, name, email)
SELECT SYS_GUID(), name, email
FROM ext_users_csv;

Monitoring and Performance ​

UUID-related performance queries:

sql
-- Check UUID column statistics
SELECT 
    column_name,
    data_type,
    data_length,
    num_distinct,
    density
FROM user_tab_col_statistics 
WHERE table_name = 'USERS' AND column_name = 'ID';

-- Index usage for UUID columns
SELECT 
    i.index_name,
    i.uniqueness,
    ic.column_name,
    ic.column_position
FROM user_indexes i
JOIN user_ind_columns ic ON i.index_name = ic.index_name
WHERE ic.table_name = 'USERS'
ORDER BY i.index_name, ic.column_position;

-- Query execution plans for UUID lookups
EXPLAIN PLAN FOR
SELECT * FROM users WHERE id = HEXTORAW('550E8400E29B41D4A716446655440000');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

UUID Version Comparison ​

Choose the right approach for your Oracle application:

  • SYS_GUID() - Oracle's native UUID generation (similar to Version 4)
  • Version 1 - Time-based UUID (custom implementation needed)
  • Version 3 - MD5 namespace-based (custom implementation)
  • Version 5 - SHA-1 namespace-based (custom implementation)

For Oracle applications:

  • Enterprise systems: Use SYS_GUID() for maximum compatibility
  • Distributed systems: SYS_GUID() works excellently across Oracle RAC
  • Data warehousing: Consider sequence-based keys for better performance

How do I generate UUID in other databases? ​

SQL databases:

  • PostgreSQL - Comprehensive UUID support with extensions
  • MySQL - Built-in UUID() function
  • SQL Server - NEWID() and NEWSEQUENTIALID()
  • SQLite - Custom functions and extensions

NoSQL databases:

  • MongoDB - ObjectId and UUID generation
  • Redis - UUID generation with Lua scripts
  • Cassandra - Built-in UUID functions

← Back to Online UUID Generator