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

Albert Minich <Albert_77_at_gmx.net> wrote in message news:<Xns93F0AAD323BE9110oo011_at_193.96.115.84>...
> ColoC meinte:
>
> > Hi, is there someone to help me?
> >
> > I have got a data duplication case in a big Oracle 8 table. Many of
> > the records were processed and loaded twice, meanwhile there are much
> > more good records without duplication. The table has 60 fields for
> > each record.
> >
> > Is there a simple Oracle sqlplus way to capture all these duplications
> > and remove the duplications?
> >
> > Thanks.
> >
> > ColoC
>
> Hi ColoC,
>
> you can use self-join:
>
> ------
> declare
> cursor cur_dup is select a.rowid
> from your_table a, your_table b
> where a.uk_column1 = b.uk_column1
> and a.uk_column2 = b.uk_column2
> and ...
> and a.rowid != b.rowid;
> v_rowid rowid;
>
> begin
>
> open cur_dup;
> loop
> fetch cur_dup into v_rowid;
> exit when cur_dup%notfound;
> delete from your_table
> where rowid = v_rowid;
> end loop;
> close cur_dup;
>
> end;
> /
>
> ------
> That is realy fast!
>
> HTH
> Albert

I have to tell you the truth that I am not an Oracle expert, I know little about PL, I wish I could try this out.

But this is the production database, and we do not have another for testing -- more important the DBA makes it a rule that operations are not allowed to do any PL in this production database.

Sorry that I can not try this. How big is your table if you ever tried this out? And how long does it take -- please give some statistical description if possible.

Thanks.

ColoC Received on Mon Sep 08 2003 - 11:34:38 CDT