-
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
-
Originally Posted by davey23uk
for ever huh? how scientific
Any update and advice on above issue.
-
yeah, do some investigation and provide some analysis
-
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
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|