Re: easy Import problem

create a new table (TA1) from the current database table. this table will exist only until you have verified full recovery. truncate current table (TA). import old data from TA into truncated TA table. The difference between the two tables (i.e., TA1 minus TA) should be the .5M "new" rows you set aside in the first step. So, create a new table TA2 to hold the rows to be written back to table TA (create table TA2 as
select * from TA1 minus select * from TA). Now, insert rows from TA2 into TA. Verify that TA is now fully recovered. Drop tables TA1 and TA2.

This has one drawback. U must have storage space for TA, TA1, and TA2. But this will allow you to avoid the unique constraints since you're only recovering "new" data.

To insure that only "new" rows are recovered, double check primary keys columns in TA2 before importing. This is because table subtraction will produce a row if any column values are not alike.

Sybrand Bakker wrote:

> export the current table to a separate file
> truncate the current table
> imp the 'old' export
> imp the 'new' export
> No other solution if you don't have sufficient space as partial import of a
> table is impossible.
>
> Hth,
> --
> Sybrand Bakker, Oracle DBA
> Wong Yew Loon <yewloon_at_geocities.com> wrote in message
> news:387AE354.64696FB7_at_geocities.com...
> > Hi,
> >
> > I have a export file of a very large table of about 5 million rows which
> > I need to import in my database. However, the table already exist in my
> > database which contains many new rows (approx. 0.5 million) plus about 4
> > million that is duplicated in the export file. Note that this table has
> > also got several unique indices.
> >
> > The last time I have tried is that I have import the 5 million row
> > export file and let it keeps complaining about the Unique constraints.
> > However, as this is taking too much time and is draining the server
> > resources.
> >
> > Anybody has got any idea on how to complete the import of the 5 million
> > row export file in the database more efficiently, best is I can import
> > only the select 1 million rows which does not exist in the database.
> >
> > I have a problem that I do not have sufficient space in my server to
> > create another table or tablespace or database to perform this task
> > separately.
> >
> > Pls help.....
> >
> > cheers,
> > Yew Loon
> >
> >
> >

Received on Wed Jan 12 2000 - 00:44:08 CST