EXPORT with Tables having CLOB datatype is SLOW

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 9.2.0.6.0
Any help on above is highly appreciated.
Regards

for ever huh? how scientific

Any update and advice on above.
Regards

Quote:

---

Originally Posted by davey23uk

for ever huh? how scientific

---

Any update and advice on above issue.

yeah, do some investigation and provide some analysis

Quote:

---

Originally Posted by davey23uk

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

exp system@abcvora1 tables=abc_TExT Direct=y file=C:\ABS.bak

kindly advice.

doesnt seem to bad, still no details though

What are you waiting on, what analysis of the situation have you done

Run the export command with suitable value for RECORDLENGTH parameter.

The max value is 64KB.

Also, increase buffer size.

Tamil

Quote:

---

Originally Posted by davey23uk

doesnt seem to bad, still no details though

What are you waiting on, what analysis of the situation have you done

---

Let me know what details should I mail to you.

Quote:

---

Originally Posted by tamilselvan

Run the export command with suitable value for RECORDLENGTH parameter.

The max value is 64KB.

Also, increase buffer size.

Tamil

---

Thanks for the tips, that were really helpful I exported with the same table with following syntax and export took 36mins.
WAY-1
Exp system@INTORA1 TABLES= (COL.ABC_TEXT) DIRECT=Y RECODELENTH=65335 FILE=C:\ABC_TEXT.dmp

WAY-2
Exp system@INTORA1 TABLES= (COL.ABC_TEXT) FILE=C:\ABC_TEXT.dmp BUFFER=37748736

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.

You reply on above is highly appreciated.

mahsnj , PMFJI, but the "Oracle9i Database Utilities" manual, in chapter 1 - Export, says :

"Rows in tables that contain objects and LOBs will now be exported using conventional path, even if direct path was specified."

SO, I pass a BUFFER param always, so IF my direct-path ends being "downgraded" to an conventional path due the LOBs it uses the correct BUFFER, and if not BUFFER is ignored (at least this have been my experience).

[]s

Chiappa

why do you need to make an export? Have you tried to copy the data via db link?

Any Update

ummm you should be providing an update with what you have done

Finally Option-2 works fine for me.
Exp system@INTORA1 TABLES= (COL.ABC_TEXT) FILE=C:\ABC_TEXT.dmp BUFFER=37748736
Thanks all for there help.
regards