Re: PROBLEM: Cannot rebuild index!!


"BD" <bobby_dread@xxxxxxxxxxx> wrote in message
news:1135970508.391367.248550@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi all.
>
> I have a problem which I'm not sure how to deal with.
>
> Here's the scenario (8i on AIX):
>
> We have a system wherein some data warehouse tables are truncated and
> repopulated each night with fresh data.
>
> This morning, a unique index on one of these tables was in an unuseable
> state. I attempted to rebuild it, and got the error ORA-01452: cannot
> CREATE UNIQUE INDEX; duplicate keys found.
>
> I've looked on Metalink, and The only sense I can make of this is that
> when the new data was inserted, a duplicate row was created.
>
> I believe that this data insert was done via SQLLoader. How the inserts
> succeeded and got past the unique index, I'm not sure.
>
> There's a script on metalink to 'delete' duplicate rows (included
> below), but I would like to identify the duplicates before deleting
> them. I have changed the script from 'delete from' to 'select * from',
> so I can view the data. But I've so far waited 10 minutes for results,
> and for a 17000 column table that strikes me as not very promising.
>
> So, in short, my question is: How should I deal with suspected
> duplicate rows when my indexes are not working?
>
> Hope someone can help!
>
> BD
>
> ***here's the Metalink script***
>
> REM This is an example SQL*Plus Script to delete duplicate rows from
> REM a table.
> REM
> set echo off
> set verify off heading off
> undefine t
> undefine c
> prompt
> prompt
> prompt Enter name of table with duplicate rows
> prompt
> accept t prompt 'Table: '
> prompt
> select 'Table '||upper('&&t') from dual;
> describe &&t
> prompt
> prompt Enter name(s) of column(s) which should be unique. If more than
> prompt one column is specified , you MUST separate with commas.
> prompt
> accept c prompt 'Column(s): '
> prompt
> delete from &&t
> where rowid not in (select min(rowid) from &&t group by &&c)
> /
>


Perhaps this is overly simplistic, but can you view the duplicate rows with:

Select f1
From t1
Group By f1
Having Count(f1)>1

BTW - I hope that table has 17000 rows rather than 17000 columns.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

.

Relevant Pages

  • Re: PROBLEM: Cannot rebuild index!!
    ... > CREATE UNIQUE INDEX; duplicate keys found. ... > There's a script on metalink to 'delete' duplicate rows (included ... > REM a table. ...
    (comp.databases.oracle.server)
  • PROBLEM: Cannot rebuild index!!
    ... CREATE UNIQUE INDEX; duplicate keys found. ... There's a script on metalink to 'delete' duplicate rows (included ... REM a table. ...
    (comp.databases.oracle.server)
  • Re: FTP Transfer
    ... REM Change Directory where the files to be transferred are. ... echo ftpusername> FtpScr.txt ... echo prompt>> FtpScr.txt ... REM lcd to change to the directory where the ...
    (microsoft.public.windows.server.scripting)
  • CMD.exe batch for net use
    ... Apologies if I should be using msdos batch group. ... XP and had a simple batch that had 3 variables at the prompt to create ... rem %1 is IP address of target ... echo %User% ...
    (microsoft.public.windowsxp.general)