Improving the imp speed

[mkrishnanand]

Hi,

I am having a table which is having around 11 million records. Now I have to take this table from one DB to another one so I am using exp and imp. But we want to use SQLLoader so how to extract the tables into flat files using PL/SQL. Any Help?

Regds

Krish

[davey23uk]

well dont have to use plsql search for flat on asktom.oracle.com

use perl

or spool from sqlplus concatenating the columns together.

use utl_file to write to an os file concatting the columns together.

However 11 million records isnt very much at all, imp shouldnt take too long to do that, few minutes i reckon

[engiri]

If a dblink can be provided, use create table as select
or copy command.. the fastest way..

[kris123]

hi hun,

So far no one beats sqlloader direct path speed in loading data,
so if u knew ur data well yo can spool it using..

edit spool1.sql
---------------
set echo off
set pages 0
set feedback off
set verify off
set term off
set heading off
spool TABLE_NAME.TXT
select '~'||column1||'~'||column2||'~'...||'~' from table_name;
spool off
exit

if date format is not the default use to_char(date_column,'yyyymmdd hh:mi:ss')

use the .ctl delimited by '~'

I often use this but not tried with tables having images saved

[engiri]

Direct load
Spooling time + loading time

import
Export +import time

Create table or copy table through link
only the create table or copy table time.
(use nologging option..)

Which one you prefer if you can have a dblink??

[nagarjuna]

Originally posted by engiri
Direct load
Spooling time + loading time

import
Export +import time

Create table or copy table through link
only the create table or copy table time.
(use nologging option..)

Which one you prefer if you can have a dblink??

Direct load.

[slimdave]

Obviously, the answer is to understand and benchmark the different processes, and decide which makes better sense in your own environment based on performance and manageability.

[Mr.Hanky]

db_link is a variable, we have db_links that link boxes in NJ to boxes in Singapore, obviously this type of mileage can slowdown network traffic. So using a db_link is not always the best solution.

Personally I would exp, zip, FTP, import.

[marist89]

Originally posted by Mr.Hanky

Personally I would exp, zip, FTP, import.

You forgot "Use a parfile".

[srt]

Import the table with indexes=n