Printable View
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. :rolleyes:
1. CTAS in parallel
2. EXP
3. FTP
4. IMP
Tamil
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.
1. CTAS temp_table as select * from your_Table where condition=...Quote:
Originally posted by tamilselvan
1. CTAS in parallel
I've got long columns..
Meet Ben Dover.
I would probably create an empty copy of a table, populate via pl/sql in chunks and then exp/imp.
I've already addressed that. Pl/sql loop is dumb.
OK genius, how else you going to do it?Quote:
Originally posted by Axr2
I've already addressed that. Pl/sql loop is dumb.
PRO*C, Host Array
BTW nologigng does exist, extracted from 7.3.4 doc
Code: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.
also copy but look what the doc says (7.3.4)
Code: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
==========
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
Marist - That was the question in the first place. If I had the solution, I wouldn't have posted a question.Quote:
OK genius, how else you going to do it?
-------
Tamil - Thanks for the suggestion. I wasn't thinking straight.
Pando - What was that about Pro*C and Host array?
Caution:
Neither PL/SQL proc nor COPY command will work correctly when long column has more than 32K bytes data, b/c the copy command works as the same way of pl/sql.
C program is the best option. You can easily handle long column if it has more than 32K size.
Tamil
You have a solution, you just think its "dumb". So you are saying you would rather wait and have an elegent solution then write a quick pl/sql loop and have it done in about an hour. Makes sense to me... :rolleyes:Quote:
Originally posted by Axr2
Marist - That was the question in the first place. If I had the solution, I wouldn't have posted a question.
Whatever man. Just pasting some bull without reading the post in entirety, doesn't cut it - except upping your counts to a billion.
I have multiple table extracts to perform. Common sense dictates not to loop forever.
yeah, I need thatQuote:
Originally posted by Axr2
except upping your counts to a billion.
Has anyone suggested a nologging CTAS?
CTAS does not work with long columns
PRO*C is just a C Program written in Oracle C language. I have seen shops which uses Pro*C in DWH ETL processes and they are damn fast. Host array is a Pro*C option, it's just like sqlplus set arraysize, fetching X rows instad of one by one
Joke!Quote:
Originally posted by pando
CTAS does not work with long columns
? :confused:Quote:
Originally posted by slimdave
Joke!
Not true. COPY command has no limitation in data size of a long column at all and works totaly different as PL/SQL.Quote:
Originally posted by tamilselvan
Neither PL/SQL proc nor COPY command will work correctly when long column has more than 32K bytes data, b/c the copy command works as the same way of pl/sql.
You can set SQL*PLus's parameter LONG to anything up to 2GB. And the setting of LONG parameter is the amount of single LONG value that COPY will deal with - the rest will be truncated. And AFAIK the maximum size of LONG can't be larger than 2GB, so tere is no actual limitation at all with COPY regarding LONGs.