Skip to content

Oracle to HeliosDB Migration Guide

Version: 1.0 Last Updated: January 2026 Compatibility Target: Oracle 11g, 12c, 18c, 19c, 21c, 23ai

Table of Contents

  1. Introduction
  2. Compatibility Overview
  3. Pre-Migration Assessment
  4. Conceptual Mapping
  5. Step-by-Step Migration
  6. Feature Mapping
  7. PL/SQL Migration
  8. Application Connectivity
  9. Post-Migration Validation
  10. Performance Considerations
  11. Common Issues and Troubleshooting
  12. Rollback Procedures

1. Introduction

1.1 Why Migrate from Oracle to HeliosDB?

Organizations are increasingly migrating from Oracle Database to HeliosDB for several compelling reasons:

Cost Savings

Cost Factor Oracle HeliosDB Savings
Processor License (Enterprise) $47,500/core Open pricing 60-80%
Annual Support 22% of license Included 100%
RAC/Clustering Additional license Built-in 100%
Partitioning $11,500/core Included 100%
Advanced Security $15,000/core Included 100%
In-Memory $23,000/core Included 100%

Typical 4-year TCO Reduction: 65-85%

Multi-Model Support

HeliosDB provides native support for multiple data models through a single platform:

  • Relational SQL: Full Oracle SQL compatibility
  • Document/JSON: MongoDB-compatible document storage
  • Key-Value: Redis-compatible operations
  • Time-Series: Native time-series optimizations
  • Graph: Cypher query language support
  • Vector: AI/ML embeddings with similarity search

Modern Architecture Advantages

Capability Oracle HeliosDB
Cloud-Native Partial Full Kubernetes support
Multi-Protocol No 9+ protocols
Auto-Scaling Manual/Licensed Automatic
Self-Healing Manual intervention AI-driven autonomous
ML Integration External tools Native in-database ML
HTAP Separate systems Unified workload

1.2 Migration Goals

This guide helps you achieve:

  1. Zero data loss during migration
  2. Minimal downtime through parallel operation strategies
  3. Application compatibility with existing Oracle clients
  4. Performance parity or improvement post-migration
  5. Reduced operational complexity through HeliosDB automation

2. Compatibility Overview

2.1 Oracle Protocol Support in HeliosDB

HeliosDB implements the Oracle TNS wire protocol and provides comprehensive Oracle 23ai compatibility:

Category Coverage Notes
SQL Language 95%+ Full Oracle SQL dialect
PL/SQL 95% Complete execution engine
DBMS Packages 85% Core packages fully implemented
Hierarchical Queries 93% Full CONNECT BY support
JSON Functions 94% Oracle 21c+ JSON support
Pseudo-columns 95% ROWNUM, ROWID, LEVEL
Analytics 92% Window functions

2.2 Supported Oracle Features

Fully Supported (Direct Migration)

  • DDL: CREATE TABLE, INDEX, VIEW, MATERIALIZED VIEW, SEQUENCE, SYNONYM
  • DML: SELECT, INSERT, UPDATE, DELETE, MERGE, INSERT ALL
  • Transactions: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
  • PL/SQL: Procedures, Functions, Packages, Triggers
  • Hierarchical: CONNECT BY, START WITH, LEVEL, SYS_CONNECT_BY_PATH
  • Advanced: PIVOT/UNPIVOT, MERGE, Flashback queries
  • JSON: JSON_VALUE, JSON_QUERY, JSON_TABLE, JSON_OBJECT
  • Analytics: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, LISTAGG

Partially Supported (Requires Modification)

  • MODEL clause (basic support)
  • BFILE data type (read-only)
  • ORA_ROWSCN (partial tracking)
  • Some DBMS_STATS functions

Not Supported (Requires Redesign)

Oracle Feature HeliosDB Alternative
Oracle RAC HeliosDB distributed clustering
Data Guard HeliosDB multi-region replication
Advanced Queuing (AQ) Native streaming + Kafka integration
Spatial (SDO_*) HeliosDB geospatial indexing
Label Security Row-level security policies
Oracle Text HeliosDB full-text search

2.3 Connection Compatibility

HeliosDB accepts Oracle client connections via TNS protocol:

Connection Port: 1521 (default)
Protocol: Oracle TNS
Wire Protocol Version: 12.2 compatible

Supported drivers: - Oracle JDBC (21c+) - cx_Oracle / oracledb (Python) - ODP.NET (21c+) - oracledb (Node.js 6.x) - godror (Go)

3. Pre-Migration Assessment

3.1 Database Inventory

Before migration, perform a comprehensive inventory of your Oracle environment.

Schema Analysis Script

-- Database size summary
SELECT
    owner,
    segment_type,
    COUNT(*) as object_count,
    ROUND(SUM(bytes)/1024/1024/1024, 2) as size_gb
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
GROUP BY owner, segment_type
ORDER BY size_gb DESC;

-- Table and index counts per schema
SELECT
    owner,
    (SELECT COUNT(*) FROM dba_tables WHERE owner = u.username) as tables,
    (SELECT COUNT(*) FROM dba_indexes WHERE owner = u.username) as indexes,
    (SELECT COUNT(*) FROM dba_views WHERE owner = u.username) as views,
    (SELECT COUNT(*) FROM dba_sequences WHERE owner = u.username) as sequences,
    (SELECT COUNT(*) FROM dba_procedures WHERE owner = u.username) as procedures
FROM dba_users u
WHERE username NOT IN ('SYS', 'SYSTEM', 'OUTLN')
ORDER BY username;

3.2 PL/SQL Code Analysis

Analyze your PL/SQL codebase for migration complexity:

-- Count PL/SQL objects by type
SELECT
    owner,
    type,
    COUNT(*) as object_count,
    SUM(CASE WHEN status = 'VALID' THEN 1 ELSE 0 END) as valid_count,
    SUM(CASE WHEN status = 'INVALID' THEN 1 ELSE 0 END) as invalid_count
FROM dba_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY',
                       'TRIGGER', 'TYPE', 'TYPE BODY')
  AND owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner, type
ORDER BY owner, object_count DESC;

-- Count lines of PL/SQL code
SELECT
    owner,
    type,
    SUM(line) as total_lines
FROM dba_source
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner, type
ORDER BY total_lines DESC;

DBMS Package Usage Inventory

Identify which DBMS packages your code uses:

-- Find DBMS package dependencies
SELECT DISTINCT
    owner,
    name,
    referenced_name as dbms_package,
    type
FROM dba_dependencies
WHERE referenced_owner = 'SYS'
  AND referenced_name LIKE 'DBMS_%'
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY referenced_name, owner, name;

-- Common patterns to search for in source code
SELECT owner, name, type, line, text
FROM dba_source
WHERE UPPER(text) LIKE '%DBMS_OUTPUT%'
   OR UPPER(text) LIKE '%DBMS_LOB%'
   OR UPPER(text) LIKE '%DBMS_SQL%'
   OR UPPER(text) LIKE '%DBMS_UTILITY%'
   OR UPPER(text) LIKE '%DBMS_SCHEDULER%'
   OR UPPER(text) LIKE '%UTL_FILE%'
   OR UPPER(text) LIKE '%DBMS_AQ%'
   OR UPPER(text) LIKE '%DBMS_XMLGEN%'
ORDER BY owner, name, line;

3.3 Schema Complexity Evaluation

Data Type Inventory

-- Identify data types in use
SELECT
    data_type,
    COUNT(*) as column_count,
    COUNT(DISTINCT owner||'.'||table_name) as table_count
FROM dba_tab_columns
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY data_type
ORDER BY column_count DESC;

Data Type Migration Risk Assessment:

Risk Level Data Types
Low (Direct) NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB, RAW
Medium (Verify) BFILE, XMLTYPE, INTERVAL, LONG, LONG RAW
High (Convert) SDO_GEOMETRY, ANYDATA, User-defined types

Constraint Analysis

-- Foreign key relationships
SELECT
    a.owner,
    a.table_name,
    COUNT(*) as fk_count
FROM dba_constraints a
WHERE a.constraint_type = 'R'
  AND a.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY a.owner, a.table_name
HAVING COUNT(*) > 5
ORDER BY fk_count DESC;

-- Check constraints using Oracle-specific functions
SELECT owner, table_name, constraint_name, search_condition
FROM dba_constraints
WHERE constraint_type = 'C'
  AND owner NOT IN ('SYS', 'SYSTEM')
  AND (search_condition LIKE '%ROWNUM%'
       OR search_condition LIKE '%SYSDATE%'
       OR search_condition LIKE '%DECODE%');

3.4 Application Dependencies

Document all applications connecting to Oracle:

-- Active session analysis
SELECT
    machine,
    program,
    module,
    COUNT(*) as session_count,
    COUNT(DISTINCT username) as user_count
FROM v$session
WHERE type = 'USER'
GROUP BY machine, program, module
ORDER BY session_count DESC;

3.5 Migration Complexity Score

Calculate your migration complexity:

Factor Points Your Score
Tables < 50 1
Tables 50-200 2
Tables > 200 3
PL/SQL LOC < 10K 1
PL/SQL LOC 10K-100K 2
PL/SQL LOC > 100K 3
No DBMS_AQ usage 0
Uses DBMS_AQ 2
No Oracle RAC 0
Uses Oracle RAC 2
No Spatial data 0
Uses SDO_GEOMETRY 3

Total Score Interpretation: - 1-4: Simple migration (1-2 weeks) - 5-8: Medium complexity (2-6 weeks) - 9+: Complex migration (6+ weeks)

4. Conceptual Mapping

4.1 Oracle to HeliosDB Object Mapping

Oracle Concept HeliosDB Equivalent Notes
Database Database 1:1 mapping
Tablespace Storage Pool Different internal management
Schema Schema Direct mapping
Table Table Full compatibility
Index Index B-tree, Bitmap supported
Sequence Sequence Compatible syntax
Synonym Synonym Public/Private supported
View View Full SQL support
Materialized View Materialized View Refresh options supported
Package Package Full PL/SQL package support
Procedure Procedure Direct mapping
Function Function Direct mapping
Trigger Trigger All trigger types
DBLink Foreign Data Wrapper Different implementation
Directory Storage Mount File system access

4.2 Tablespace to Storage Pool Mapping

Oracle tablespaces map to HeliosDB storage pools with different characteristics:

-- Oracle tablespace query
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces;

HeliosDB Storage Pool Configuration:

-- Create storage pool (HeliosDB)
CREATE STORAGE POOL sales_pool
    WITH REPLICATION_FACTOR = 3
    COMPRESSION = 'lz4'
    ENCRYPTION = 'aes256';

-- Assign table to pool
CREATE TABLE sales.orders (
    order_id NUMBER PRIMARY KEY,
    order_date DATE
) STORAGE POOL sales_pool;

Mapping Recommendations:

Oracle Tablespace Type HeliosDB Storage Pool
SYSTEM/SYSAUX system_pool (auto-managed)
UNDO Not needed (MVCC internal)
TEMP Automatic temp management
User data Custom storage pools
Index tablespace Same pool as data (optional)

4.3 User and Role Mapping

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

-- HeliosDB: Create user
CREATE USER app_user WITH PASSWORD 'password'
    DEFAULT SCHEMA app_user;

-- Oracle: Grant roles
GRANT CONNECT, RESOURCE TO app_user;
GRANT SELECT ON sales.orders TO app_user;

-- HeliosDB: Grant roles (compatible syntax)
GRANT CONNECT, RESOURCE TO app_user;
GRANT SELECT ON sales.orders TO app_user;

4.4 Sequence Migration

-- Oracle sequence
CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    MAXVALUE 999999999
    CACHE 100
    CYCLE;

-- HeliosDB sequence (compatible syntax)
CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    MAXVALUE 999999999
    CACHE 100
    CYCLE;

-- Usage is identical
SELECT order_seq.NEXTVAL FROM DUAL;
INSERT INTO orders (id) VALUES (order_seq.NEXTVAL);

4.5 Synonym Migration

-- Oracle public synonym
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- HeliosDB (same syntax)
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- Private synonym
CREATE SYNONYM my_emp FOR hr.employees;

5. Step-by-Step Migration

5.1 Phase 1: Schema Export Using Data Pump

Full Schema Export

# Oracle Data Pump export
expdp system/password@ORCL \
    SCHEMAS=HR,SALES,INVENTORY \
    DIRECTORY=DUMP_DIR \
    DUMPFILE=schema_export_%U.dmp \
    LOGFILE=schema_export.log \
    PARALLEL=4 \
    CONTENT=ALL \
    COMPRESSION=ALL

Schema-Only Export (for translation)

# Export DDL only
expdp system/password@ORCL \
    SCHEMAS=HR \
    DIRECTORY=DUMP_DIR \
    DUMPFILE=ddl_export.dmp \
    LOGFILE=ddl_export.log \
    CONTENT=METADATA_ONLY

Generate SQL DDL Scripts

-- Using DBMS_METADATA
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET PAGESIZE 0
SET LINESIZE 1000

-- Export all tables in schema
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner)
FROM dba_tables
WHERE owner = 'HR';

-- Export all indexes
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner)
FROM dba_indexes
WHERE owner = 'HR'
  AND index_type != 'LOB';

-- Export all procedures/functions
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, owner)
FROM dba_objects
WHERE owner = 'HR'
  AND object_type = 'PROCEDURE';

-- Export all packages
SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, owner)
FROM dba_objects
WHERE owner = 'HR'
  AND object_type = 'PACKAGE';

5.2 Phase 2: Schema Translation for HeliosDB

Most Oracle DDL works directly in HeliosDB. Common translations needed:

Storage Clause Removal

-- Oracle DDL with storage clauses
CREATE TABLE employees (
    employee_id NUMBER(10) NOT NULL,
    first_name VARCHAR2(50),
    hire_date DATE DEFAULT SYSDATE
)
TABLESPACE users
STORAGE (INITIAL 64K NEXT 64K)
PCTFREE 10 PCTUSED 40;

-- HeliosDB equivalent (remove storage specifics)
CREATE TABLE employees (
    employee_id NUMBER(10) NOT NULL,
    first_name VARCHAR2(50),
    hire_date DATE DEFAULT SYSDATE
);
-- Or with storage pool:
-- ) STORAGE POOL users_pool;

Partitioning Translation

-- Oracle range partitioning
CREATE TABLE sales_history (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- HeliosDB (compatible syntax)
CREATE TABLE sales_history (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

Index Translation

-- Oracle bitmap index
CREATE BITMAP INDEX idx_status ON orders(status);

-- HeliosDB (same syntax)
CREATE BITMAP INDEX idx_status ON orders(status);

-- Oracle function-based index
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

-- HeliosDB (same syntax)
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

5.3 Phase 3: Data Migration Strategies

HeliosDB can process Oracle Data Pump files directly:

-- Connect to HeliosDB
sqlplus admin/password@//heliosdb-server:1521/heliosdb

-- Import from Data Pump file
IMPORT DATAPUMP FROM '/path/to/schema_export.dmp'
    SCHEMAS = HR, SALES
    TRANSFORM = STORAGE:N
    PARALLEL = 8;

Strategy B: SQL*Loader Format Migration

# Export data from Oracle using SQL*Loader
sqlldr userid=system/password@ORCL \
    control=export.ctl \
    data=employees.csv \
    log=export.log

Control file (export.ctl): 

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET UTF8
INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    employee_id,
    first_name,
    last_name,
    hire_date DATE "YYYY-MM-DD",
    salary
)

Import to HeliosDB: 

-- Using COPY command
COPY employees FROM '/path/to/employees.csv'
WITH (FORMAT csv, HEADER true);

Strategy C: Parallel ETL with Batch Processing

For large tables (>100GB), use parallel extraction:

-- Oracle: Extract in chunks
SELECT /*+ PARALLEL(e, 8) */
    employee_id, first_name, last_name, hire_date, salary
FROM employees e
WHERE MOD(ROWNUM, 8) = 0;  -- Run 8 parallel streams

HeliosDB Batch Insert: 

-- Use batch insert for performance
BEGIN BATCH INSERT
    INSERT INTO employees VALUES (1, 'John', 'Doe', DATE '2020-01-15', 75000);
    INSERT INTO employees VALUES (2, 'Jane', 'Smith', DATE '2020-02-20', 82000);
    -- ... more rows
END BATCH;

5.4 Phase 4: PL/SQL Migration

Most PL/SQL code migrates directly. See Section 7 for details.

5.5 Phase 5: Application Cutover

Parallel Running Strategy

Week 1-2: Read-only HeliosDB testing
Week 3:   Write to both, read from Oracle
Week 4:   Write to both, read from HeliosDB
Week 5:   HeliosDB primary, Oracle standby
Week 6:   Decommission Oracle reads

Connection String Changes

# Oracle connection
oracle_dsn = "oracle+cx_oracle://user:pass@oracle-host:1521/ORCL"

# HeliosDB connection (same driver works)
helios_dsn = "oracle+cx_oracle://user:pass@heliosdb-host:1521/heliosdb"

6. Feature Mapping

6.1 Oracle SQL Extensions in HeliosDB

DECODE Function

-- Oracle DECODE (fully supported)
SELECT
    employee_id,
    DECODE(status,
           'A', 'Active',
           'I', 'Inactive',
           'T', 'Terminated',
           'Unknown') as status_desc
FROM employees;

NVL and NVL2

-- NVL (replace NULL)
SELECT NVL(commission, 0) FROM employees;

-- NVL2 (if not null / if null)
SELECT NVL2(manager_id, 'Has Manager', 'Top Level') FROM employees;

ROWNUM and ROWID

-- ROWNUM for limiting
SELECT * FROM employees WHERE ROWNUM <= 10;

-- ROWID for row identification
SELECT ROWID, employee_id FROM employees WHERE department_id = 10;

6.2 DBMS Package Mapping

DBMS_OUTPUT

-- Fully supported
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    DBMS_OUTPUT.PUT_LINE('Processing started');
    DBMS_OUTPUT.PUT('Status: ');
    DBMS_OUTPUT.PUT_LINE('Complete');
    DBMS_OUTPUT.NEW_LINE;
END;
/

DBMS_LOB

-- LOB operations (fully supported)
DECLARE
    v_clob CLOB;
    v_length NUMBER;
BEGIN
    SELECT document INTO v_clob FROM documents WHERE id = 1;

    v_length := DBMS_LOB.GETLENGTH(v_clob);
    DBMS_OUTPUT.PUT_LINE('Length: ' || v_length);

    -- Substring
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_clob, 100, 1));

    -- Append
    DBMS_LOB.APPEND(v_clob, ' Additional text');
END;
/

DBMS_SQL

-- Dynamic SQL (fully supported)
DECLARE
    v_cursor INTEGER;
    v_rows INTEGER;
BEGIN
    v_cursor := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(v_cursor,
        'UPDATE employees SET salary = salary * 1.1 WHERE department_id = :dept',
        DBMS_SQL.NATIVE);

    DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept', 10);

    v_rows := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_OUTPUT.PUT_LINE('Updated ' || v_rows || ' rows');

    DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

DBMS_SCHEDULER

-- Job scheduling (fully supported)
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'NIGHTLY_CLEANUP',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'cleanup_old_data',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
        enabled         => TRUE
    );
END;
/

-- Manage jobs
BEGIN
    DBMS_SCHEDULER.RUN_JOB('NIGHTLY_CLEANUP');
    DBMS_SCHEDULER.DISABLE('NIGHTLY_CLEANUP');
    DBMS_SCHEDULER.DROP_JOB('NIGHTLY_CLEANUP');
END;
/

DBMS_METADATA

-- Schema export (fully supported)
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'EMPLOYEES', 'HR') FROM DUAL;

UTL_FILE

-- File I/O (fully supported)
DECLARE
    v_file UTL_FILE.FILE_TYPE;
    v_line VARCHAR2(32767);
BEGIN
    v_file := UTL_FILE.FOPEN('DATA_DIR', 'output.txt', 'W');
    UTL_FILE.PUT_LINE(v_file, 'Header line');

    FOR rec IN (SELECT * FROM employees) LOOP
        UTL_FILE.PUT_LINE(v_file, rec.employee_id || ',' || rec.first_name);
    END LOOP;

    UTL_FILE.FCLOSE(v_file);
END;
/

6.3 Partitioning Support

-- Range partitioning
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE
) PARTITION BY RANGE (sale_date) (
    PARTITION p_q1 VALUES LESS THAN (DATE '2024-04-01'),
    PARTITION p_q2 VALUES LESS THAN (DATE '2024-07-01'),
    PARTITION p_q3 VALUES LESS THAN (DATE '2024-10-01'),
    PARTITION p_q4 VALUES LESS THAN (DATE '2025-01-01')
);

-- List partitioning
CREATE TABLE orders (
    order_id NUMBER,
    region VARCHAR2(20)
) PARTITION BY LIST (region) (
    PARTITION p_east VALUES ('NY', 'NJ', 'CT'),
    PARTITION p_west VALUES ('CA', 'WA', 'OR'),
    PARTITION p_other VALUES (DEFAULT)
);

-- Hash partitioning
CREATE TABLE customers (
    customer_id NUMBER,
    name VARCHAR2(100)
) PARTITION BY HASH (customer_id) PARTITIONS 16;

6.4 Flashback to Time Travel Mapping

Oracle Flashback maps to HeliosDB Time Travel:

-- Oracle Flashback Query
SELECT * FROM employees AS OF TIMESTAMP
    TO_TIMESTAMP('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- HeliosDB Time Travel (identical syntax)
SELECT * FROM employees AS OF TIMESTAMP
    TO_TIMESTAMP('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Oracle Flashback with SCN
SELECT * FROM employees AS OF SCN 12345678;

-- HeliosDB (identical syntax)
SELECT * FROM employees AS OF SCN 12345678;

-- Versions between timestamps
SELECT versions_starttime, versions_endtime, versions_operation,
       employee_id, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
    TO_TIMESTAMP('2024-01-01', 'YYYY-MM-DD') AND
    TO_TIMESTAMP('2024-01-31', 'YYYY-MM-DD')
WHERE employee_id = 100;

HeliosDB Time Travel Advantages: - Configurable retention (7+ days default) - Delta compression (<10% storage overhead) - Sub-millisecond temporal queries - Integrated with MVCC

7. PL/SQL Migration

7.1 Supported PL/SQL Constructs

HeliosDB provides 95% PL/SQL compatibility:

Variable Declarations

DECLARE
    -- Scalar types
    v_number NUMBER(10,2) := 100.50;
    v_string VARCHAR2(100) := 'Hello';
    v_date DATE := SYSDATE;
    v_timestamp TIMESTAMP := SYSTIMESTAMP;
    v_boolean BOOLEAN := TRUE;

    -- Constants
    c_tax_rate CONSTANT NUMBER := 0.08;

    -- Anchored types
    v_salary employees.salary%TYPE;
    v_emp_row employees%ROWTYPE;

    -- Collections
    TYPE num_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_numbers num_array;

    -- Nested tables
    TYPE name_list IS TABLE OF VARCHAR2(100);
    v_names name_list := name_list('John', 'Jane', 'Bob');
BEGIN
    -- Body
    NULL;
END;
/

Control Flow

DECLARE
    v_grade CHAR(1);
    v_score NUMBER := 85;
BEGIN
    -- IF-THEN-ELSE
    IF v_score >= 90 THEN
        v_grade := 'A';
    ELSIF v_score >= 80 THEN
        v_grade := 'B';
    ELSIF v_score >= 70 THEN
        v_grade := 'C';
    ELSE
        v_grade := 'F';
    END IF;

    -- CASE expression
    v_grade := CASE
        WHEN v_score >= 90 THEN 'A'
        WHEN v_score >= 80 THEN 'B'
        WHEN v_score >= 70 THEN 'C'
        ELSE 'F'
    END;

    -- Simple LOOP
    LOOP
        v_score := v_score - 10;
        EXIT WHEN v_score < 0;
    END LOOP;

    -- WHILE loop
    v_score := 100;
    WHILE v_score > 0 LOOP
        v_score := v_score - 10;
    END LOOP;

    -- FOR loop
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
    END LOOP;

    -- FOR loop with REVERSE
    FOR i IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Countdown: ' || i);
    END LOOP;
END;
/

Cursors

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

    -- Cursor with parameters
    CURSOR dept_cursor(p_dept_id NUMBER) IS
        SELECT * FROM employees WHERE department_id = p_dept_id;

    v_emp emp_cursor%ROWTYPE;
BEGIN
    -- Explicit cursor operations
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
    END LOOP;
    CLOSE emp_cursor;

    -- Cursor FOR loop (implicit)
    FOR rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary);
    END LOOP;

    -- Cursor with parameters
    FOR rec IN dept_cursor(20) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.first_name);
    END LOOP;
END;
/

BULK COLLECT and FORALL

DECLARE
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    TYPE id_tab IS TABLE OF NUMBER;

    v_employees emp_tab;
    v_ids id_tab := id_tab(1, 2, 3, 4, 5);
BEGIN
    -- BULK COLLECT
    SELECT *
    BULK COLLECT INTO v_employees
    FROM employees
    WHERE department_id = 10;

    DBMS_OUTPUT.PUT_LINE('Collected ' || v_employees.COUNT || ' rows');

    -- FORALL with SAVE EXCEPTIONS
    FORALL i IN v_ids.FIRST..v_ids.LAST SAVE EXCEPTIONS
        UPDATE employees
        SET salary = salary * 1.1
        WHERE employee_id = v_ids(i);

EXCEPTION
    WHEN OTHERS THEN
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Error at index ' ||
                SQL%BULK_EXCEPTIONS(j).ERROR_INDEX);
        END LOOP;
END;
/

7.2 Exception Handling

DECLARE
    v_salary NUMBER;

    -- Custom exception
    e_salary_too_high EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_salary_too_high, -20001);
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 999;  -- May not exist

    IF v_salary > 1000000 THEN
        RAISE e_salary_too_high;
    END IF;

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

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

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

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;
END;
/

7.3 Stored Procedures

CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_account IN NUMBER,
    p_to_account   IN NUMBER,
    p_amount       IN NUMBER,
    p_status       OUT VARCHAR2
) AS
    v_from_balance NUMBER;
    e_insufficient_funds EXCEPTION;
BEGIN
    -- Check source balance
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;

    IF v_from_balance < p_amount THEN
        RAISE e_insufficient_funds;
    END IF;

    -- Perform transfer
    UPDATE accounts SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    UPDATE accounts SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    COMMIT;
    p_status := 'SUCCESS';

EXCEPTION
    WHEN e_insufficient_funds THEN
        ROLLBACK;
        p_status := 'INSUFFICIENT_FUNDS';
    WHEN OTHERS THEN
        ROLLBACK;
        p_status := 'ERROR: ' || SQLERRM;
END transfer_funds;
/

7.4 Packages

-- Package specification
CREATE OR REPLACE PACKAGE employee_pkg AS
    -- Types
    TYPE emp_rec IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(100),
        salary NUMBER
    );

    TYPE emp_tab IS TABLE OF emp_rec;

    -- Constants
    c_max_salary CONSTANT NUMBER := 500000;

    -- Procedures
    PROCEDURE hire_employee(
        p_name IN VARCHAR2,
        p_dept_id IN NUMBER,
        p_salary IN NUMBER
    );

    -- Functions
    FUNCTION get_employee_count(p_dept_id IN NUMBER) RETURN NUMBER;
    FUNCTION get_department_employees(p_dept_id IN NUMBER) RETURN emp_tab PIPELINED;
END employee_pkg;
/

-- Package body
CREATE OR REPLACE PACKAGE BODY employee_pkg AS

    PROCEDURE hire_employee(
        p_name IN VARCHAR2,
        p_dept_id IN NUMBER,
        p_salary IN NUMBER
    ) AS
    BEGIN
        IF p_salary > c_max_salary THEN
            RAISE_APPLICATION_ERROR(-20001, 'Salary exceeds maximum');
        END IF;

        INSERT INTO employees (employee_id, employee_name, department_id, salary)
        VALUES (emp_seq.NEXTVAL, p_name, p_dept_id, p_salary);

        COMMIT;
    END hire_employee;

    FUNCTION get_employee_count(p_dept_id IN NUMBER) RETURN NUMBER AS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count
        FROM employees
        WHERE department_id = p_dept_id;

        RETURN v_count;
    END get_employee_count;

    FUNCTION get_department_employees(p_dept_id IN NUMBER)
        RETURN emp_tab PIPELINED AS
    BEGIN
        FOR rec IN (
            SELECT employee_id, employee_name, salary
            FROM employees
            WHERE department_id = p_dept_id
        ) LOOP
            PIPE ROW(emp_rec(rec.employee_id, rec.employee_name, rec.salary));
        END LOOP;
        RETURN;
    END get_department_employees;

END employee_pkg;
/

7.5 Triggers

-- Row-level trigger
CREATE OR REPLACE TRIGGER emp_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSIF DELETING THEN
        v_action := 'DELETE';
    END IF;

    INSERT INTO emp_audit (
        audit_id,
        employee_id,
        action,
        action_date,
        old_salary,
        new_salary,
        changed_by
    ) VALUES (
        audit_seq.NEXTVAL,
        NVL(:NEW.employee_id, :OLD.employee_id),
        v_action,
        SYSDATE,
        :OLD.salary,
        :NEW.salary,
        USER
    );
END;
/

-- Statement-level trigger
CREATE OR REPLACE TRIGGER dept_stats_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
DECLARE
BEGIN
    -- Update department statistics
    MERGE INTO department_stats t
    USING (
        SELECT department_id, COUNT(*) as emp_count, AVG(salary) as avg_salary
        FROM employees
        GROUP BY department_id
    ) s ON (t.department_id = s.department_id)
    WHEN MATCHED THEN
        UPDATE SET t.emp_count = s.emp_count, t.avg_salary = s.avg_salary
    WHEN NOT MATCHED THEN
        INSERT (department_id, emp_count, avg_salary)
        VALUES (s.department_id, s.emp_count, s.avg_salary);
END;
/

-- INSTEAD OF trigger (for views)
CREATE OR REPLACE TRIGGER emp_view_trigger
INSTEAD OF INSERT ON emp_department_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (employee_id, employee_name, department_id)
    VALUES (:NEW.employee_id, :NEW.employee_name, :NEW.department_id);
END;
/

7.6 Conversion Patterns for Unsupported Features

DBMS_AQ (Advanced Queuing) to HeliosDB Streaming

-- Oracle AQ
DBMS_AQ.ENQUEUE(
    queue_name => 'order_queue',
    message_properties => msg_props,
    payload => order_message
);

-- HeliosDB: Use streaming table
INSERT INTO order_stream (order_id, order_data, created_at)
VALUES (123, '{"customer": "ABC"}', CURRENT_TIMESTAMP);

-- Consumer reads from stream
SELECT * FROM order_stream
WHERE processed = FALSE
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;

Oracle Spatial to HeliosDB Geospatial

-- Oracle SDO_GEOMETRY
SELECT * FROM stores
WHERE SDO_WITHIN_DISTANCE(
    location,
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.98, 40.75, NULL), NULL, NULL),
    'distance=10 unit=km'
) = 'TRUE';

-- HeliosDB geospatial
SELECT * FROM stores
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(-73.98, 40.75), 4326),
    10000  -- meters
);

8. Application Connectivity

8.1 TNS Configuration

tnsnames.ora for HeliosDB

HELIOSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = heliosdb-server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = heliosdb)
    )
  )

# High availability with multiple hosts
HELIOSDB_HA =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = helios1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = helios2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = helios3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = heliosdb)
    )
  )

8.2 JDBC Configuration

import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;

public class HeliosDBConnection {
    public static void main(String[] args) throws SQLException {
        // Simple connection
        String url = "jdbc:oracle:thin:@//heliosdb-server:1521/heliosdb";
        Connection conn = DriverManager.getConnection(url, "user", "password");

        // With connection pool
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@//heliosdb-server:1521/heliosdb");
        ods.setUser("user");
        ods.setPassword("password");

        // Pool settings
        ods.setConnectionCacheEnabled(true);
        ods.setConnectionCacheName("HELIOS_POOL");

        Properties cacheProps = new Properties();
        cacheProps.setProperty("MinLimit", "5");
        cacheProps.setProperty("MaxLimit", "50");
        cacheProps.setProperty("InitialLimit", "10");
        ods.setConnectionCacheProperties(cacheProps);

        Connection pooledConn = ods.getConnection();
    }
}

8.3 Python (oracledb) Configuration

import oracledb

# Thin mode (no Oracle Client needed)
oracledb.init_oracle_client()  # Optional for thick mode

# Simple connection
connection = oracledb.connect(
    user="admin",
    password="password",
    dsn="heliosdb-server:1521/heliosdb"
)

# Connection pool
pool = oracledb.create_pool(
    user="admin",
    password="password",
    dsn="heliosdb-server:1521/heliosdb",
    min=5,
    max=50,
    increment=5,
    threaded=True,
    getmode=oracledb.POOL_GETMODE_WAIT
)

# Get connection from pool
with pool.acquire() as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM employees WHERE department_id = :1", [10])
        for row in cursor:
            print(row)

8.4 Node.js (oracledb) Configuration

const oracledb = require('oracledb');

// Connection pool
async function initPool() {
    await oracledb.createPool({
        user: 'admin',
        password: 'password',
        connectString: 'heliosdb-server:1521/heliosdb',
        poolMin: 5,
        poolMax: 50,
        poolIncrement: 5,
        poolTimeout: 60
    });
}

// Execute query
async function runQuery() {
    let connection;
    try {
        connection = await oracledb.getConnection();

        const result = await connection.execute(
            `SELECT employee_id, first_name, salary
             FROM employees
             WHERE department_id = :deptId`,
            { deptId: 10 },
            { outFormat: oracledb.OUT_FORMAT_OBJECT }
        );

        console.log(result.rows);
    } finally {
        if (connection) {
            await connection.close();
        }
    }
}

8.5 .NET (ODP.NET) Configuration

using Oracle.ManagedDataAccess.Client;

// Connection string
string connString = @"
    User Id=admin;
    Password=password;
    Data Source=heliosdb-server:1521/heliosdb;
    Pooling=true;
    Min Pool Size=5;
    Max Pool Size=50;
    Connection Timeout=30";

using (OracleConnection conn = new OracleConnection(connString))
{
    conn.Open();

    using (OracleCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM employees WHERE department_id = :dept";
        cmd.Parameters.Add("dept", OracleDbType.Int32).Value = 10;

        using (OracleDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["EMPLOYEE_ID"]}: {reader["FIRST_NAME"]}");
            }
        }
    }
}

9. Post-Migration Validation

9.1 Data Integrity Validation

Row Count Verification

-- Generate comparison queries
SELECT
    'SELECT ''' || table_name || ''' as table_name, COUNT(*) as row_count FROM ' ||
    owner || '.' || table_name || ';' as validation_query
FROM dba_tables
WHERE owner = 'HR'
ORDER BY table_name;

-- Execute on both Oracle and HeliosDB, compare results

Checksum Validation

-- Oracle: Generate row checksums
SELECT
    table_name,
    ORA_HASH(
        LISTAGG(column_value, '|') WITHIN GROUP (ORDER BY column_name)
    ) as table_checksum
FROM (
    SELECT
        c.table_name,
        c.column_name,
        SUM(ORA_HASH(c.column_name)) as column_value
    FROM dba_tab_columns c
    WHERE c.owner = 'HR'
    GROUP BY c.table_name, c.column_name
)
GROUP BY table_name;

-- Compare with HeliosDB
SELECT
    table_name,
    HASH(STRING_AGG(column_value::text, '|' ORDER BY column_name)) as table_checksum
FROM (...)
GROUP BY table_name;

9.2 Functional Validation

Stored Procedure Testing

-- Create test harness
CREATE TABLE migration_test_results (
    test_id NUMBER PRIMARY KEY,
    test_name VARCHAR2(200),
    test_type VARCHAR2(50),
    oracle_result VARCHAR2(4000),
    helios_result VARCHAR2(4000),
    match_status VARCHAR2(20),
    test_date TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Test procedure execution
DECLARE
    v_oracle_result VARCHAR2(4000);
    v_helios_result VARCHAR2(4000);
BEGIN
    -- Execute on Oracle
    v_oracle_result := test_procedure_on_oracle();

    -- Execute on HeliosDB
    v_helios_result := test_procedure_on_heliosdb();

    INSERT INTO migration_test_results (
        test_id, test_name, test_type,
        oracle_result, helios_result,
        match_status
    ) VALUES (
        test_seq.NEXTVAL, 'calculate_salary', 'FUNCTION',
        v_oracle_result, v_helios_result,
        CASE WHEN v_oracle_result = v_helios_result THEN 'PASS' ELSE 'FAIL' END
    );
END;
/

9.3 Performance Validation

Query Performance Comparison

-- Capture Oracle execution times
SET TIMING ON
SET AUTOTRACE ON

SELECT /*+ Oracle baseline */ *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;

-- Compare with HeliosDB
EXPLAIN ANALYZE
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;

9.4 Validation Checklist

Category Validation Item Status
Data Row counts match [ ]
Data Checksums match [ ]
Data Sample data spot checks [ ]
Schema All tables created [ ]
Schema All indexes created [ ]
Schema All constraints active [ ]
Schema All sequences created [ ]
PL/SQL Procedures compile [ ]
PL/SQL Functions return correct values [ ]
PL/SQL Packages compile [ ]
PL/SQL Triggers fire correctly [ ]
Security Users created [ ]
Security Grants applied [ ]
Performance Key queries perform acceptably [ ]
Application Connection successful [ ]
Application CRUD operations work [ ]
Application Transactions commit/rollback [ ]

10. Performance Considerations

10.1 HeliosDB Performance Features

HeliosDB provides automatic performance optimizations:

Feature Description Benefit
Auto-Indexing ML-based index recommendations 30-50% query improvement
Adaptive Query Execution Runtime plan optimization Handles data skew
Global Distributed Cache Multi-tier caching 10-100x repeated queries
Query Pattern Learning Learns access patterns Proactive optimization
Predicate Pushdown Filter pushdown to storage 3-10x faster scans

10.2 Index Optimization

-- HeliosDB auto-index recommendation
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123 AND order_date > DATE '2024-01-01';

-- Review index recommendations
SELECT * FROM heliosdb_index_recommendations
WHERE table_name = 'orders';

-- Apply recommended indexes
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

10.3 Statistics Collection

-- Analyze tables after migration
ANALYZE TABLE employees COMPUTE STATISTICS;

-- For large tables, use sampling
ANALYZE TABLE large_orders ESTIMATE STATISTICS SAMPLE 10 PERCENT;

-- Schedule regular statistics updates
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'NIGHTLY_STATS',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN ANALYZE; END;',
        repeat_interval => 'FREQ=DAILY; BYHOUR=3'
    );
END;
/

10.4 Query Tuning

-- Enable query hints (Oracle-compatible)
SELECT /*+ INDEX(e idx_emp_dept) */ *
FROM employees e
WHERE department_id = 10;

-- Parallel query execution
SELECT /*+ PARALLEL(orders, 8) */
    customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

-- Use EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

10.5 Memory and Resource Tuning

# HeliosDB configuration (heliosdb.toml)
[oracle.memory]
pga_aggregate_target = "4GB"
sort_area_size = 256000
hash_area_size = 512000

[oracle.optimizer]
mode = "ALL_ROWS"
cost_based = true
query_rewrite = true

[cache]
query_result_cache_size = "1GB"
metadata_cache_size = "256MB"

11. Common Issues and Troubleshooting

11.1 Connection Issues

Error: ORA-12154 (TNS Alias Not Found)

Cause: TNS name not in tnsnames.ora or LDAP

Solution: 

# Verify tnsnames.ora location
echo $TNS_ADMIN
cat $TNS_ADMIN/tnsnames.ora

# Test with Easy Connect
sqlplus admin/password@//heliosdb-server:1521/heliosdb

Error: ORA-12541 (No Listener)

Cause: HeliosDB Oracle protocol not running

Solution: 

# Check HeliosDB Oracle listener status
heliosdb status --protocol oracle

# Verify port is open
netstat -an | grep 1521
telnet heliosdb-server 1521

11.2 SQL Compatibility Issues

Error: Invalid Column Name/Type

Cause: Reserved word or unsupported type

Solution: 

-- Quote reserved words
SELECT "LEVEL", "SIZE", "DATE" FROM my_table;

-- Check for unsupported types
SELECT column_name, data_type
FROM user_tab_columns
WHERE data_type IN ('SDO_GEOMETRY', 'ANYDATA', 'BFILE');

Error: PL/SQL Compilation Error

Cause: Unsupported syntax or package

Solution: 

-- Show compilation errors
SHOW ERRORS PROCEDURE my_procedure;

-- Check for unsupported packages
SELECT name, text
FROM user_source
WHERE UPPER(text) LIKE '%DBMS_AQ%'  -- Not supported
   OR UPPER(text) LIKE '%SDO_%';    -- Not supported

11.3 Data Migration Issues

Large Object (LOB) Migration Errors

Cause: LOB size or corruption

Solution: 

-- Verify LOB integrity on Oracle
SELECT table_name, column_name, segment_name
FROM dba_lobs
WHERE owner = 'HR';

-- Check for corrupted LOBs
SELECT id, DBMS_LOB.GETLENGTH(document) as lob_length
FROM documents
WHERE DBMS_LOB.GETLENGTH(document) IS NULL;

Character Set Conversion Issues

Cause: Different character sets

Solution: 

-- Check Oracle character set
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

-- Configure HeliosDB to match
ALTER SESSION SET NLS_CHARACTERSET = 'AL32UTF8';

11.4 Performance Issues

Slow Queries After Migration

Cause: Missing statistics or indexes

Solution: 

-- Rebuild statistics
ANALYZE TABLE slow_table COMPUTE STATISTICS;

-- Check for missing indexes
SELECT * FROM heliosdb_index_recommendations
WHERE estimated_improvement > 50;

-- Force index usage if needed
SELECT /*+ INDEX(t idx_name) */ * FROM slow_table t WHERE ...;

High Memory Usage

Cause: Large result sets or inefficient queries

Solution: 

-- Use pagination
SELECT * FROM large_table
WHERE ROWNUM <= 1000
OFFSET 0 ROWS;

-- Enable result set streaming
SET ARRAYSIZE 100

11.5 Error Code Mapping

Oracle Error HeliosDB Error Resolution
ORA-00001 Unique constraint violation Same handling
ORA-00054 Resource busy Retry transaction
ORA-00060 Deadlock detected Auto-resolved, retry
ORA-01403 No data found Same handling
ORA-01422 Too many rows Same handling
ORA-01555 Snapshot too old Increase retention
ORA-04031 Shared pool exhausted Increase cache size

12. Rollback Procedures

12.1 Pre-Migration Rollback Preparation

Before starting migration, ensure rollback capability:

-- Create rollback savepoint in Oracle
CREATE RESTORE POINT pre_migration GUARANTEE FLASHBACK DATABASE;

-- Document current Oracle configuration
SELECT name, value FROM v$parameter ORDER BY name;

-- Export application connection strings
-- Document all TNS entries

12.2 During Migration Rollback

If migration fails mid-process:

# Stop HeliosDB data ingestion
heliosdb migration pause --job-id <migration-job>

# Verify Oracle is still primary
sqlplus admin/password@ORACLE_PRIMARY

# Check Oracle Data Guard sync status (if using)
SELECT database_role, open_mode FROM v$database;

12.3 Post-Migration Rollback

If issues are discovered after cutover:

Step 1: Switch Back to Oracle

# Update DNS/Load balancer to point to Oracle
# Update application connection strings

# For applications using TNS
# Edit tnsnames.ora to point back to Oracle
PRODUCTION =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-server)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )

Step 2: Sync Data Back to Oracle

-- If using parallel write during migration
-- Data should already be in Oracle

-- If not, export from HeliosDB
EXPORT TABLE changed_data TO '/backup/changes.dmp'
WHERE modified_date > TO_DATE('2024-01-15', 'YYYY-MM-DD');

-- Import to Oracle
impdp system/password@ORCL \
    DUMPFILE=changes.dmp \
    DIRECTORY=DUMP_DIR \
    TABLE_EXISTS_ACTION=REPLACE

Step 3: Restore Oracle from Flashback

-- If Oracle Flashback was configured
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT pre_migration;
ALTER DATABASE OPEN RESETLOGS;

12.4 Rollback Checklist

Step Action Status
1 Stop HeliosDB writes [ ]
2 Verify Oracle connectivity [ ]
3 Update connection strings [ ]
4 Test application connectivity [ ]
5 Sync any delta data [ ]
6 Validate data integrity [ ]
7 Resume Oracle operations [ ]
8 Notify stakeholders [ ]

12.5 Rollback Decision Criteria

Consider rollback if:

  • Data integrity issues that cannot be resolved within SLA
  • Performance degradation >50% on critical queries
  • Application functionality failures affecting users
  • Security vulnerabilities discovered
  • Unplanned extended downtime

Do NOT rollback for:

  • Minor performance differences (optimize instead)
  • Cosmetic issues in monitoring/tooling
  • Non-critical feature differences
  • Issues with clear workarounds

Appendix A: Quick Reference Card

Essential Commands

-- Connect to HeliosDB via Oracle protocol
sqlplus admin/password@//heliosdb:1521/heliosdb

-- Check Oracle compatibility mode
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL;

-- View active sessions
SELECT * FROM v$session WHERE type = 'USER';

-- Check PL/SQL compilation
SHOW ERRORS PROCEDURE <name>;

-- Analyze performance
EXPLAIN PLAN FOR <query>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Configuration Files

File Location Purpose
tnsnames.ora $TNS_ADMIN/ Connection aliases
heliosdb.toml /etc/heliosdb/ Server configuration
listener.ora $TNS_ADMIN/ Not needed for HeliosDB

Support Resources

  • HeliosDB Oracle Protocol Documentation: /docs/reference/protocols/oracle/
  • Migration Tools: heliosdb migrate --help
  • Support: support@heliosdb.io

Appendix B: Migration Timeline Template

Week Phase Activities
1 Assessment Inventory, complexity analysis
2 Planning Design migration strategy, create test plan
3-4 Schema Migration Export DDL, translate, create in HeliosDB
5-6 Data Migration Initial data load, verify
7-8 PL/SQL Migration Convert procedures, functions, packages
9 Testing Functional testing, performance testing
10 Parallel Run Dual-write, validate
11 Cutover Switch primary to HeliosDB
12 Stabilization Monitor, optimize, close out

Related Documentation: - Oracle Protocol Configuration - Oracle Compatibility Reference - Oracle SQL Examples - General Migration Guide

Document History: - v1.0 (January 2026): Initial comprehensive guide