DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: exporting large table

  1. #1
    Join Date
    Mar 2001
    Posts
    41

    exporting large table

    Hello,

    Can someone help me on this.

    I need to create a test db for our application team base on our existing production but less data. Because of space constraint on the test server I need only to replicate the structure of the production db and populate only 5 huges tables. One of the table is 18G in size and doing an export on the table will give me no less than 18G dump file. Problem is, windows only support 2g large file and im using oracle 7.3 which doesnot support FILESIZE parameter for export.

    What will i do?

    Thank u in advance..

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857

    Re: exporting large table

    Originally posted by hanselrl
    Hello,

    Can someone help me on this.

    I need to create a test db for our application team base on our existing production but less data. Because of space constraint on the test server I need only to replicate the structure of the production db and populate only 5 huges tables. One of the table is 18G in size and doing an export on the table will give me no less than 18G dump file. Problem is, windows only support 2g large file and im using oracle 7.3 which doesnot support FILESIZE parameter for export.

    What will i do?

    Thank u in advance..
    hello hanselrl,

    create the test database.
    create db link in test database referencing the schema owner of the 5 tables.
    create the table such as:
    create table table_name as
    select * from schema_owner.tab_name@yourdblink.com
    where rownum < 2000;

    or whatever condition that satisfy your requirements.

    regards,
    reydp

  3. #3
    Join Date
    Mar 2001
    Posts
    41
    Hi Reydp,

    I forgot to tell that this table has a column type "LONG RAW". Doing a "create.. as select" I think wont work on this type..

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by hanselrl
    Hi Reydp,

    I forgot to tell that this table has a column type "LONG RAW". Doing a "create.. as select" I think wont work on this type..


    So I suppose INSERT statement from a SELECT statement also won't work?

    Here is the only thing that I can think fast right now.
    Upgrade your db version to at least 8i, and then do an EXPORT using TRANSPORT_TABLESPACE.
    ---------------

  5. #5
    Join Date
    Mar 2001
    Posts
    41
    Are there any other suggestions?

  6. #6
    Join Date
    Jan 2003
    Location
    Delhi
    Posts
    63
    if u have any unix box , u can try the export for NT db from unix server

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by hanselrl
    Are there any other suggestions?
    make your test server with linux O.S.(lower version).
    Install at least 8i db( I assume no 7.3 db version for linux).
    Do an export from there.
    And import it to your test db.
    ---------------

  8. #8
    Join Date
    Mar 2001
    Posts
    41
    Originally posted by reydp
    make your test server with linux O.S.(lower version).
    Install at least 8i db( I assume no 7.3 db version for linux).
    Do an export from there.
    And import it to your test db.

    I dont it wont work coz you cannnot do an 8i export on a 7.3 db.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Since you need a test db, you can create 5 temp tables without LONG column for the original table. Insert rows as select into the temp tables with less data.
    Export the tables and imp them into test db.

    Add the long column in the test db.
    Ask the appln team to update on the LONG column with dummy data.

    Tamil

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    If long row column doesn't play much relevance in your test db, then Tamil's way can do the workaround.
    ---------------

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