Re: EXPORT Question

From: <mjain_at_my-dejanews.com>
Date: Tue, 15 Sep 1998 08:07:53 GMT
Message-ID: <6tl78n$98l$1@nnrp1.dejanews.com>

Let us say your big 10GB table name is bigtable.

You can create 10 tables with exact same structure as bigtable by using following SQL statements:-

Create table smalltable1 as select * from bigtable where 1=2;
Create table smalltable2 as select * from bigtable where 1=2;
Create table smalltable3 as select * from bigtable where 1=2;
Create table smalltable4 as select * from bigtable where 1=2;
Create table smalltable5 as select * from bigtable where 1=2;
Create table smalltable6 as select * from bigtable where 1=2;
Create table smalltable7 as select * from bigtable where 1=2;
Create table smalltable8 as select * from bigtable where 1=2;
Create table smalltable9 as select * from bigtable where 1=2;

Create table smalltable10 as select * from bigtable where 1=2;

Do a row count of bigtable, say value is no_of_rows_in_bigtable. Now write a PL/SQL stored procedure to populate smalltables from bigtable so that each table has almost same number of rows.

Algorithm:-

1. Get the value of no_of_rows_in_bigtable.
2. Evaluate ceil(no_of_rows_in_bigtable/10) this will give you approximate number of rows you should put in each table. Say you call this value as rows_per_small_table.
3. Initialize a counter say rowsinsertedcounter = 0.
4. Read a row from bigtable.
5. Insert this row into table smalltable1.
6. Increment counter rowsinsertedcounter.
7. Is ( rowsinserted = rows_per_small_table )? If No --> go back to step 4. If yes --> issue commit, reinitialize rowsinsertedcounter to 0.

Now do steps 4 to 7 and insert rows into smalltable2 this time,

then do steps 4 to 7 and insert rows into smalltable3 this time,

.....

thne do steps 4 to 7 and and insert rows smalltable10 this time.

This is just to give you a method to work around, so that you have smaller tables which you can export.

After you export them,

when you are ready to import them, then import smalltable1 to Smalltable10.

Make a bigtable and insert rows from smalltable1 to smalltable10 in the bigtable.

This is just to give you an idea or a possible method.

This may be a time consuming exercise and may not be the best thing possible with the size of table that you have got.

I just thought I will mention it here anyway.

Hope this helps,

Manoj Jain

In article <6tk6ae$34e$1_at_nnrp1.dejanews.com>,   proveen_at_hotmail.com wrote:
> Hi,
>
> If i need to export a big table of size 10GB or more, i think
> i will have problems on Unix Systems when the size is more than 3GB.
>
> So i guess i have to split into mutiple dump files.
> I don't think it's a problem on VMS systems.
>
> So could anyone tell me how to export a big table of size more than 10GB into
> multiple dump files?
>
> Thank you
> Praveen
> proveen_at_hotmail.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Sep 15 1998 - 03:07:53 CDT