Hi,

We have a requirement to convert SQL database 2008 into Oracle RAC. It is being performed using Oracle SQL developer(v 4.0.2.15) via offline migration
We are facing performance issues while loading data into oracle mainly due to huge size of one of the table in SQL, around 300GB with approx. 220 GB of BLOB data distributed among two columns. The issues is being faced at the below two levels. Pls help in improving the performance at these two points

Loading data :::
Loading the data into oracle from flat files , extracted from SQL via bcp utility scripts generated by the tool
I am already using direct load (direct=true parallel=true and unrecoverable) via SQL*Loader but still it is taking time, might be due to large BLOB size. Any proposition to tune it better or use anything else to load the data into oracle from flat files

Converting CLOB to BLOB ::
After successful loading of data, CLOBTOBLOB_sqldeveloper is taking too much time to convert the loaded CLOB data into BLOB in one of the post scripts generated by tool. Is there any other better way, in terms of performance, to convert CLOB to BLOB or directly move BLOB data from SQL to oracle. Complete procedure below for your reference

----- Procedure START -----
CREATE OR REPLACE PROCEDURE CLOBTOBLOB_sqldeveloper
(
tableNameL VARCHAR2 ,
clobColumnNameL VARCHAR2,
blobColumnNameL VARCHAR2 )
AS
tableName VARCHAR2 ( 500 ) := '';--to_UPPER(tableNameL);
clobColumnName VARCHAR2 ( 500 ) := '';--to_UPPER(clobColumNameL);
blobColumnName VARCHAR2 ( 500 ) := '';--to_UPPER(blobColumNameL);
tmpString VARCHAR2 ( 500 ) := '';
errorOut BOOLEAN := false;
inputLength NUMBER; -- size of input CLOB
offSet NUMBER := 1;
pieceMaxSize NUMBER := 500; -- the max size of each peice large for 12c
piece VARCHAR2 ( 500 CHAR ) ; -- these pieces will make up the entire CLOB
currentPlace NUMBER := 1; -- this is where were up to in the CLOB
blobLoc BLOB; -- blob locator in the table
clobLoc CLOB; -- clob locator pointsthis is the value from the dat file
myquery VARCHAR2 ( 2000 ) ;
-- THIS HAS TO BE CHANGED FOR SPECIFIC CUSTOMER TABLE
-- AND COLUMN NAMES
--CURSOR cur;
TYPE cur_typ
IS
REF
CURSOR;
cur cur_typ;
--cur_rec cur%ROWTYPE;
BEGIN
tableName := UPPER ( tableNameL ) ;
clobColumnName := UPPER ( clobColumnNameL ) ;
blobColumnName := UPPER ( blobColumnNameL ) ;
BEGIN
EXECUTE immediate 'select table_name from user_tables where table_name = :1 ' INTO tmpString USING tableName;
IF ( tmpString != tableName ) THEN
errorOut := true;
ELSE
BEGIN
EXECUTE immediate 'select COLUMN_NAME from user_tab_columns where table_name = :1 and COLUMN_NAME = :2 ' INTO tmpString USING tableName, clobColumnName;
IF ( tmpString != clobColumnName ) THEN
errorOut := true;
ELSE
EXECUTE immediate 'select COLUMN_NAME from user_tab_columns where table_name = :1 and COLUMN_NAME = :2 ' INTO tmpString USING tableName, blobColumnName;
IF ( tmpString != blobColumnName ) THEN
errorOut := true;
END IF;
END IF;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
errorOut := true;
END;
IF ( errorOut = true ) THEN
raise_application_error ( -20001, 'Invalid parameters' ) ;
END IF;
EXECUTE immediate 'update ' || tableName || ' set ' || blobColumnName || '= empty_blob() ' ;
myquery := 'SELECT '||clobColumnName||' clob_column , '||blobColumnName||' blob_column FROM ' || tableName || ' FOR UPDATE';
OPEN cur FOR myquery;-- using clobColumName, blobColumnName ;
FETCH cur
INTO clobLoc,
blobLoc ;

WHILE cur%FOUND
LOOP
--RETRIVE THE clobLoc and blobLoc
--clobLoc := cur_rec.clob_column;
--blobLoc := cur_rec.blob_column;
currentPlace := 1; -- reset evertime
-- find the lenght of the clob
inputLength := DBMS_LOB.getLength ( clobLoc ) ;
-- loop through each peice
LOOP
IF (inputLength > 1) /* if originally zero length, could have a chr(0) character */
THEN
-- get the next piece and add it to the clob
piece := DBMS_LOB.subStr ( clobLoc,pieceMaxSize,currentPlace ) ;
-- append this piece to the BLOB
DBMS_LOB.WRITEAPPEND ( blobLoc, LENGTH ( piece ) /2, HEXTORAW ( piece ) ) ;
END IF;
currentPlace := currentPlace + pieceMaxSize ;
EXIT
WHEN inputLength < currentplace;
END LOOP;
FETCH cur
INTO clobLoc,
blobLoc ;
END LOOP;
EXECUTE immediate 'alter table ' || tableName || ' drop column ' || clobColumnName;
--unnecessary after ddl
COMMIT;
END CLOBtoBLOB_sqldeveloper;

----- Procedure END -----

Thanks