-
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??
-
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.
-nagarjuna
-
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.
I remember when this place was cool.
-
Originally posted by Mr.Hanky
Personally I would exp, zip, FTP, import.
You forgot "Use a parfile".
Jeff Hunter
-
Import the table with indexes=n
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
|