Re: Q: Oracle sqlplus way to capture all duplications and removal.

colocoloc_at_yahoo.com (ColoC) wrote in message news:<96fc2618.0309060512.1954e7f6_at_posting.google.com>...
> vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0309050943.682cf1c3_at_posting.google.com>...
> > colocoloc_at_yahoo.com (ColoC) wrote i
> >.....
> >
> > BTW, you did not mention how big the table. If is that really big (10+
> > million rows and more), you may want to make sure that you do have
> > enough space for creating the 2nd table. You may also want to use
> > parallel query.
>
> That's the point. Even though I do not have 10+ million, I have over
> 500+k rows. And I am not the DBA who made the rule that a user (as I
> am) is not allowed to create any table in the DB. But I am asked to
> deal with the problem.
>
> My thought is that I can dump the might-be duplicated portion from DB
> to files, delete the portion in DB, use OS commands to clean up the
> files, then reload the data in the file back to DB.
>
> This seems to be the only possible way of doing it under my condition.
> Any other hints? Thanks.
>
> ColoC

If you are on unix, this is probably the quickest and easiest way to do this, with the possible exception of the delete. The delete can do screwy things from a DBA standpoint, depending on if you are using partitions and your index structure and whatnot. So one method would be, during production downtime, export table (for a backup), extract all data to flat files, manipulate data (sort -u can be very helpful), truncate table, reload data (with sqlldr). Of course, you should have a test system to try this on first, and like everyone pointed out, need DBA cooperation. There might be a variant with partitions if your problem data is suitably concentrated, where you could just replace data in the partition, but that might require more DBA cooperation.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/tue/news/news_1n9download.html