DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: 7.3.4 - best way to 'mass extract' data?

  1. #1
    Join Date
    Oct 2002
    Posts
    807

    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.
    Last edited by Axr2; 06-15-2004 at 12:47 PM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    1. CTAS in parallel
    2. EXP
    3. FTP
    4. IMP

    Tamil

  3. #3
    Join Date
    Oct 2002
    Posts
    807
    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.
    Last edited by Axr2; 06-15-2004 at 01:41 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by tamilselvan
    1. CTAS in parallel
    1. CTAS temp_table as select * from your_Table where condition=...
    Jeff Hunter

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    I've got long columns..
    Last edited by Axr2; 06-15-2004 at 01:22 PM.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Meet Ben Dover.

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

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    I've already addressed that. Pl/sql loop is dumb.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Axr2
    I've already addressed that. Pl/sql loop is dumb.
    OK genius, how else you going to do it?
    Jeff Hunter

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width