mandymag.com

Here is a simple script that I wrote and that I’ve been utilizing to cleanup individual or a batch of duplicate records in an Oracle Database. Once duplicate record unique identifier(s) have been identified, use this script to find all existences of it and delete all existences of it:

select ‘SELECT * FROM ‘||OWNER||’.'||TABLE_NAME||’ WHERE ‘||COLUMN_NAME||’ in ‘||’(comma,separated,uniquie,numeric,indentifiers);’
from sys.dba_tab_columns
where column_name like ‘%unique identifier column name%’
and OWNER in (’comma’,’separated’,'applicable’,'table’,'owner’,'list’)
order by NUM_DISTINCT;

1. Execute statement above as a script in SQL Developer, TOAD, etc.
2. Copy output from script
3. Paste output from original script into a new SQL Statement window and execute that output as a script

That will return any data found in any table or row for the table owners identified in initial script.

Once data is verified as removable, simply modify the same script from SELECT * to DELETE to remove the data:

select ‘DELETE FROM ‘||OWNER||’.'||TABLE_NAME||’ WHERE ‘||COLUMN_NAME||’ in ‘||’(comma,separated,uniquie,numeric,indentifiers);’
from sys.dba_tab_columns
where column_name like ‘%unique identifier column name%’
and OWNER in (’comma’,’separated’,'applicable’,'table’,'owner’,'list’)
order by NUM_DISTINCT;

Then follow steps 1 through 3 above again to complete removal process.

Additional note:

Originally when running this script without the “order by NUM_DISTINCT”, I was getting the following error:

ORA-02292: integrity constraint (string.string) violated - child record found
Cause: attempted to delete a parent key value that had a foreign key dependency.
Action: delete dependencies first then parent or disable constraint.

I would then need to run the script multiple times in order to make sure all instances of the duplicate rows(s) were removed. Additionally, at times, I found myself having to manually put a parent record back in before being able to remove a child record.

The “order by NUM_DISTINCT”, seemed to alleviate that error, for the most part, in my situation, given my table structure.

tags: num_distinct, ora-02292, batch duplicate record cleanup, batch duplicate row cleanup, clean up duplicate rows, duplicate record queries, oracle database duplicate rows, oracle database pl/sql queries, oracle pl/sql, order by num_distinct, pl/sql, script to clean up duplicate records, sql, sys.dba_tab_columns sql script