Improving the imp speed

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

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

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

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

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??

Quote:

---

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.

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.

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.

Quote:

---

Originally posted by Mr.Hanky

Personally I would exp, zip, FTP, import.

---

You forgot "Use a parfile".

Import the table with indexes=n

11 million rows shouldn't be such a problem. I would do a direct load through a db link. You can do this by using the APPEND hint. Here's how I would do this.

1. Create the table (no indexes or primary key) with no rows in database B
2. Create the db link in database B
3. use an insert statement like this:
insert /*+ APPEND */ into tableA (select * from tableA@databaseA);

This will bybass the undo/redo writing. You'll be amazed at the load time. I use this all the time and load about 4 million rows in 20 seconds. Remember to build the indexes/primary key after you load the table.

insert /*+ APPEND */ into tableA (select * from tableA@databaseA);
loads the data faster.
I inserted 13 million rows under 3 minutes.

with Nologging ..