Hi All Gurus,
Recently we changed Datatype from LONG to CLOB in our database tables. However when we are doing a export/Import of Schema it is long long time to complete .I do not understand as per
Oracle's documentation Oracle recommends change the datatype from LONG to CLOB.However EXP/IMP of those tables with CLOB takes for ever. Any Idea how to make it fast.
We are on Oracle 184.108.40.206.0
Any help on above is highly appreciated.
yeah, do some investigation and provide some analysis
We have table called ABC_TEXT with one columns as "MSG" and the data type was LONG I converted the LONG column to CLOB as given below.
Alter table ABC_TEXT modify msg clob;
Number of row were equal after modification i.e. "4500000" appox.
When I am exporting this table it took 3.25 Hours. That is my problem and export command is given below
Run the export command with suitable value for RECORDLENGTH parameter.
The max value is 64KB.
Also, increase buffer size.
Thanks for the tips, that were really helpful I exported with the same table with following syntax and export took 36mins.
Exp system@INTORA1 TABLES= (COL.ABC_TEXT) DIRECT=Y RECODELENTH=65335 FILE=C:\ABC_TEXT.dmp
Following is a note from Oracle Doc:
"The Buffer parameter applies only to conventional path export. It has no effect on a direct path Export. For direct path exports use RECORDLENTH parameter to specify the size of the buffer that Export uses for writing to the export file."
Both the above ways took 36 mins to complete. Just want to confirm which of the above is correct or both are correct.
First can you throw some light when you mentioned "Increase BUFFER SIZE"
Second How can I make Import process faster for above table.