7.3.4 - best way to 'mass extract' data?

[Axr2]

What's the best way to mass extract data in 7.3.4? Source table : 100 mill records. Some date condition applied to it results in 5 million records. I need to insert these 5 mill in a remote database.

Note : 7.3.4 - no concept of nologging, partitioning, export/Query etc.

A pl/sql loop will take AGES. Does not seem smart.

Any suggestions?

PS : Oh, and I am working with limited space on both machines.

[tamilselvan]

1. CTAS in parallel
2. EXP
3. FTP
4. IMP

Tamil

[Axr2]

1. CTAS - No nologging concept. Also, I've got long cols
2. EXP : Yeah direct clause..but no concept of 'query' in 7.3.4. So how do u extract a subset?
3. FTP? Huh?
4. IMP see 2.

[marist89]

Originally posted by tamilselvan
1. CTAS in parallel

1. CTAS temp_table as select * from your_Table where condition=...

[Axr2]

I've got long columns..

[marist89]

Meet Ben Dover.

I would probably create an empty copy of a table, populate via pl/sql in chunks and then exp/imp.

[Axr2]

I've already addressed that. Pl/sql loop is dumb.

[marist89]

Originally posted by Axr2
I've already addressed that. Pl/sql loop is dumb.

OK genius, how else you going to do it?

[pando]

PRO*C, Host Array

BTW nologigng does exist, extracted from 7.3.4 doc

UNRECOVERABLE



specifies that the creation of the table (and any indices required because of constraints) will not be logged in the redo log file. As a result, media recovery will not recreate the table (and any indices required because of constraints).



This keyword can only be specified with the AS subquery clause. Using this keyword makes table creation faster than using the RECOVERABLE option because redo log entries are not written.

[pando]

also copy but look what the doc says (7.3.4)

Copying Data from One Database to Another
Use the SQL*Plus COPY command to copy data between databases and between tables on the same database. With the COPY command, you can copy data between databases in the following ways:

copy data from a remote database to your local database
copy data from your local (default) database to a remote database (on most systems)
copy data from one remote database to another remote database (on most systems)
Note: In general, the COPY command was designed to be used for copying data between Oracle and non-Oracle databases. You should use SQL commands (CREATE TABLE AS and INSERT) to copy data between Oracle databases