If your Duplicates are large in number then the following method works fine..
1. create table TMP as select distinct ( <set of columns > ) from <ORIGINAL_TABLE> 2. truncate table <ORIGINAL_TABLE> 3. insert into <ORIGINAL_TABLE> select * from TMP/use SQL Loader after taking a dump from TMP. Bhulu --- David Wagoner <[EMAIL PROTECTED]> wrote: > Here is an interesting script I found on Metalink > (Note:1019920.6) for > removing duplicates, but I have not tried it yet: > > > > ====== > Title: > ====== > > Script to Eliminate Non-unique Rows > > > =========== > Disclaimer: > =========== > > This script is provided for educational purposes > only. It is NOT supported > by > Oracle World Wide Technical Support. The script has > been tested and appears > > to work as intended. However, you should always > test any script before > relying on it. > > PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to > differences in the way text > > editors, email packages and operating systems handle > text formatting > (spaces, > tabs and carriage returns), this script may not be > in an executable state > when > you first receive it. Check over the script to > ensure that errors of this > type are corrected. > > > ========= > Abstract: > ========= > > This script removes all but one row (all but the row > with the highest rowid) > > from <owner>.<table> in each group of rows having > identical values in > <column(s)>. Multiple columns must be separated > with commas (without > spaces). > > Script TFSUNIQU is intended primarily for use in > deleting rows that prevent > the creation of a unique index on the columns in > <column(s)>. It will > happily delete rows that are not identical, as long > as the rows are > identical with respect to the values of the columns > in <column(s)>. > > > ============= > Requirements: > ============= > > You must have DELETE privileges on the selected > table. > > > ======= > Script: > ======= > > ----------- cut ---------------------- cut > -------------- cut -------------- > > > SET ECHO off > REM NAME: TFSUNIQU.SQL > REM USAGE:"@path/tfsuniqu schema_name table_name > column_name(s)" > REM > ------------------------------------------------------------------------ > > REM REQUIREMENTS: > REM DELETE on selected table > REM > ------------------------------------------------------------------------ > > REM AUTHOR: > REM Grant Franjione, Phil Joel, and Cary Millsap > > REM (c)1994 Oracle Corporation > REM > ------------------------------------------------------------------------ > > REM PURPOSE: > REM Removes all but one row (all but the row with > the highest rowid) > REM from <owner>.<table> in each group of rows > having identical values > REM in <colum(s)>. Multiple columns must be > seperated with commas > REM (without spaces). > REM > REM TFSUNIQU is intended primarily for use in > deleting rows that > REM prevent the creation of a unique index on the > columns in > REM <column(s)>. It will happily delete rows > that are not identical, > REM as long as the rows are identical with > respect to the values of > REM the columns in <column(s)>. > REM > ------------------------------------------------------------------------ > > REM EXAMPLE: > REM N/A > REM > ------------------------------------------------------------------------ > > REM DISCLAIMER: > REM This script is provided for educational > purposes only. It is NOT > REM supported by Oracle World Wide Technical > Support. > REM The script has been tested and appears to > work as intended. > REM You should always run new scripts on a test > instance initially. > REM > ------------------------------------------------------------------------ > > REM Main text of script follows: > > def owner = &&1 > def table = &&2 > def uukey = &&3 > > delete from &owner..&table > where rowid in ( > select rowid from &owner..&table > minus > select min(rowid) from &owner..&table group by > &uukey > ) > / > > undef owner > undef table > undef uukey > > > > ----------- cut ---------------------- cut > -------------- cut -------------- > > > > > > David B. Wagoner > Database Administrator > Arsenal Digital Solutions Worldwide, Inc. > 8000 Regency Parkway, Suite 110 > Cary, NC 27511-8582 > Office (919) 466-6723 > Pager [EMAIL PROTECTED] > Fax (919) 466-6783 > <http://www.arsenaldigital.com/> > http://www.arsenaldigital.com/ > > > *** NOTICE *** > This e-mail message is confidential, intended only > for the named > recipient(s) above and may contain information that > is privileged, work > product or exempt from disclosure under applicable > law. If you have > received this message in error, or are not the named > recipient(s), please > immediately notify the sender by phone or email and > delete this e-mail > message from your computer. Thank you. > > -----Original Message----- > Sent: Tuesday, June 04, 2002 2:54 PM > To: Multiple recipients of list ORACLE-L > > I know I have seen this posted before....... > > We have a large range partitioned table that has > duplicates in it. What is > the fastest way to remove the dups.? I have the > following scripts which do > it but may be fast or slow. What do you guys use? > === message truncated === __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: S B INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
