Overview

This article documents a data base migration from Oracle 11g (source) to Oracle 10g (target) where typical export/import methods were unavailable due to access restrictions.

Initial Approaches Considered

1. Data Pump (expdp/impdp) Data Pump utilities were the first choice but required operating system authentication credentials that were not obtainable.
2. PL/SQL Developer Export The PL/SQL Developer data export feature was attempted, but encountered errors during the process and was abandoned.
3. Database Link Migration Given the moderate data volume, a database link approach was selected.

Implementation Steps

Creating the Database Link A public database link was created on the target database pointing to the source system:

CREATE PUBLIC DATABASE LINK
    to_source_db CONNECT TO "source_user" IDENTIFIED BY "source_password" USING 
'(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.1.68)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )';

Object Definition Retrieval DDL statements for user objects were obtained using the dbms_metadata package:

SELECT dbms_metadata.get_ddl('TABLE', 'ACT_GE_BYTEARRAY') FROM dual;

The strategy involved creating tables first, then populating them with data.

Handling Constraints During Migration Disabling constraints was necessary before data insertion. The following PL/SQL block disibles foreign key constraints and other enabled constraints:

FOR rec IN (SELECT table_name, constraint_name 
            FROM user_constraints
            WHERE constraint_type = 'R'
              AND status = 'ENABLED') LOOP
    EXECUTE IMMEDIATE 'alter table "' || rec.table_name || '" disable constraint ' ||
                      rec.constraint_name;
END LOOP;

FOR rec IN (SELECT table_name, constraint_name 
            FROM user_constraints
            WHERE status = 'ENABLED') LOOP
    EXECUTE IMMEDIATE 'alter table "' || rec.table_name || '" disable constraint ' ||
                      rec.constraint_name;
END LOOP;

LOB Field Challenge

During remote data accesss through the database link, the following error occurred:

ERROR: ORA-22992: cannot use LOB locators selected from remote tables

Several tables contained LOB columns (CLOB, BLOB), preventing direct remote access.

Solution: Global Temporary Tables

A workaround using Oracle global temporary tables was implemented. Since approximately 50 tables contained LOB fields, a cursor-based approach was used to process each table sequentially.

DECLARE
    v_table_name VARCHAR2(32);
    v_sql        VARCHAR2(2000);
    v_exists     NUMBER;
    
    CURSOR table_cursor IS
        SELECT t.table_name
          FROM user_tables t
         WHERE t.table_name IN (SELECT st.table_name
                                 FROM user_tab_columns st
                                WHERE st.data_type IN ('CLOB', 'BLOB'))
         ORDER BY t.table_name;

BEGIN
    FOR rec IN table_cursor LOOP
        v_table_name := rec.table_name;
        
        -- Clear target table
        v_sql := 'truncate table ' || v_table_name;
        EXECUTE IMMEDIATE v_sql;
        
        -- Remove any existing temp table
        SELECT COUNT(1) INTO v_exists 
        FROM user_tables 
        WHERE table_name = UPPER('tmp_table');
        
        IF v_exists > 0 THEN
            EXECUTE IMMEDIATE 'drop table tmp_table purge';
        END IF;
        
        -- Create global temporary table without preservation clause
        v_sql := 'create global temporary table tmp_table as select * from ' || v_table_name;
        EXECUTE IMMEDIATE v_sql;
        
        -- Pull remote data into temp table
        EXECUTE IMMEDIATE ('insert into tmp_table select * from ' || v_table_name || '@to_source_db');
        
        -- Insert from temp table to target
        v_sql := 'insert into ' || v_table_name || ' select * from tmp_table';
        EXECUTE IMMEDIATE v_sql;
        
        COMMIT;
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(v_table_name || ':' || sqlcode || ':' || sqlerrm);
END;

Important Discovery About Temporary Tables

Initial attempts with ON COMMIT PRESERVE ROWS caused issues. Testing revealed the following behavior:

SQL> CREATE GLOBAL TEMPORARY TABLE tmp_test AS SELECT * FROM employees;
Table created.

SQL> DROP TABLE tmp_test;
Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE tmp_test ON COMMIT PRESERVE ROWS AS SELECT * FROM employees;
Table created.

SQL> DROP TABLE tmp_test;
DROP TABLE tmp_test
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

The ON COMMIT PRESERVE ROWS clause prevents dropping the temporary table while the session remains active. Removing this clause allows the table to be dropped after each data transfer completes.

Alternative: ETL Tool Approach

Using an ETL tool like Kettle proved to be a more straightforward solution. Kettle handles LOB fields natively without requiring workarounds and automatically generates transformations for each table. Testing confirmed reliable LOB data transfer with minimal configuration overhead.