Re: Copy back-up data into an existing table

From: C Chang <cschang_at_maxinter.net>
Date: Fri, 28 Feb 2003 22:33:09 -0500
Message-ID: <3E6029F5.3AB6@maxinter.net>

Ed Prochak wrote:
>
> C Chang wrote:
> > Alistair Thomson wrote:
> >
> >>Hi
> >>
> >>Yes you can do this but you need to use the flag ignore=y (this tells the
> >>import to ignore the create table error - as it already exists - and
> >>continue loading the data). Watch out for any constraint violations eg
> >>primary key etc.
> >>
> >>Incidentally, the rows=y flag means import or export the data contained in
> >>the table. rows=no just exports the table definitions.
> >>
> >>Alistair
> >>
> >>"C Chang" <cschang_at_maxinter.net> wrote in message
> >>news:3E5D94A7.5729_at_maxinter.net...
> >>
> >>>Because of limitation of our current HD, I plan to dump out data from a
> >>>table and let the new data insert. Later when we need the old data, I
> >>>will import those data back to the table and appending to existing
> >>>data. Can I use the COPY to do this or just use the ROWS=y in imp
> >>>syntax? Never done this. Need help. Thanks ahead.
> >>>
> >>>C Chang
> >>
> > Alistair:
> > Thanks. I got this answer fro AskTome site and is trying now. But
> > according to the example from Tom, I will end up with duplicated rows of
> > records. For example, After i export the data out of the table, I
> > removed some of data before a certain date and let the table keep
> > growing with new ones. So when I import again, I will have some overlap
> > data ( from when I export to that date ). I am looking a solution to
> > remove one of the duplicated ones and keep only one left. Have any
> > suggestions? appreciate again.
> >
> > C Chang
>
> Don't you have a PRIMARY KEY on the tables?
>
> Aaaaaargh! Another file-based database implemented in ORACLE!
>
> Please, for your sake, take a Relational DB class.
>
> --
> Ed Prochak
> running: http://www.faqs.org/faqs/running-faq/
> family: http://web.magicinterface.com/~collins
> --
> "Two roads diverged in a wood and I
> I took the one less travelled by
> and that has made all the difference."
> robert frost

Ed:

   Thanks for your suggestion. I am not the original designer of this Database. I am not even the DBA or Oracle developer for our project here. I am just 1/3 tester, 1/3 web developer, 1/3 Host administor, well maybe 1/6 of SQL developer. ( that's why I asked questions in 6at least 7 different news groups), So i have no right to change the structure. Because there is a problem, my boss told me to fix it.

Anyway. I found the sql syntax to delete the duplicate ones. But somehow it did not work well. What is happened this afternoon was: Say originally

1. the table has 1000 rows of record.
2. I dump this 1000 rows out -> a dmp file
3. I deleted 990 rows out from the table using its date as condition.
4. I import the dmp file back using the ignore=y
5. Table has 1010 rows now.  Supposely 10 duplicate ones.
6. I tried the standard syntax of delete duplicated rows
    delete from T t1
    where t1.rowid <>            <==== I tried with ">" only as well
       ( select min(t2.rowid)
         from T t2
         where t1.col1 = t2.col1
         and t1.col2 = t2.col2
         and < for rest of all columns, including a object column,
         which I use t1.obj.column to reference> )

7. return with 0 rows deleted

then I tried different combindation of those columns. Of course it return with different numbers of rows deleted. So I run out of my steam. I figured out another way to accomplish my goal. Will try next Monday. Does anyone has better idea what was wrong with my syntax above? Thanks

I also tried with "group by all columns", but I got the error ORA-22950: cannot ORDER objects without MAP or ORDER method. If the table definition did not include a MAP member function for a
user defined object type column. Any method can goes around the current infrastructure or I need to change the table definition ( if I can ).

C C

C Chang Received on Fri Feb 28 2003 - 21:33:09 CST