Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

loading 20 million rows in Oracle

A batch process needs to load 20 million rows from a database table on server 1 , to a database table on server 2.  Both databses are running  on Oracle 10.1.0.5  .
The database column names, definitions and attributes on source and target tables are identical. Other than data pump, do we have any other tools/options available within Oacle to do this?

Thanks...
0
Veera_8
Asked:
Veera_8
2 Solutions
 
MarioAlcaideCommented:
Hi,

You could make a DBlink from server2 to server1, and make a CREATE TABLE AS SELECT * FROM table_name@db_link;

I think that will be the fastest way.

Regards
0
 
sventhanCommented:
You can use SQL Plus COPY command.

This does not require DBlink and its over the wire and please check for any restrictions

http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/apb.htm
0
 
paquicubaCommented:
Use NOLOGGING with CTAS if you cannot use Oracle data pump as advised above

create table tab nologging as select * from tab@link
0
 
choukssaCommented:
If possible, you can use imp/exp or impdp/expdp to get the data exported from source db and imported in destination db.  I am demonstrating expdp

You will have to create a directory
CREATE OR REPLACE DIRECTORY SCOTT_EXP_DIR  AS '/data/backups/scott'

Open in new window


Export the table from the schema

expdp schemas=SCOTT directory=SCOTT_EXP_DIR TABLES=MY_TAB1 dumpfile=MY_TAB1.dmp logfile=MY_TAB1.log

Open in new window


Import the table from the schema

impdp schemas=MARTIN directory=SCOTT_EXP_DIR TABLES=MY_TAB1 dumpfile=MY_TAB1.dmp logfile=MY_TAB1.log TABLE_EXISTS_ACTION=REPLACE

Open in new window

if the table name is same then use the switch TABLE_EXISTS_ACTION =  APPEND or REPLACE
else if it a different then get this table imported when do a  insert from select query and get rid of the table once done

--choukssa
0
 
slightwv (䄆 Netminder) Commented:
choukssa,

From the original question: "Other than data pump,"
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now