==========
original post
7.3.4 - best way to 'mass extract' data?
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.
======================
In your first post, you never said that table has a long column.
Here is the solution. The concept is simple - divide and conquer.
From the source table (100 m rows), you want to extract 5 mil rows.
I hope the table has PK/unique key.
1 create 20 dummy tables of the same structure of original table.
These 20 tables will get approximately 250,000 rows.
2 Create 20 pl/sql procedures in which you extract suitably 250,000 rows (approximately) by writing suitable where clause based on pk/uk. Or one procedure with IN parameters.
3 Export these 20 tables (fire 20 exports concurrently)
4 FTP 20 dmp files to target DB
5 In the target db, create 20 views (name should match dummy tables) on the base table.
6 Import these tables. (fire 20 imports concurrently)
Tamil
