How to delete duplicate rows from all of my oracle database tables, using PL/SQL?

Question

A generic way to remove perfectly duplicated rows from all tables owned by a certain user.

Zsolt Z. · Accepted Answer · 2017-02-21 10:18:17Z

2

Here's the stored procedure for it. Don't forget to replace 'YOUR_USERNAME_HERE' with your username.

CREATE OR REPLACE PROCEDURE DELETE_DUPLICATES_FROM_DB
IS
  TABLE_COLUMNS VARCHAR2(10000);
  DELETE_STATEMENT VARCHAR2(10000);
  CURSOR ALL_MY_TABLES IS SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'YOUR_USERNAME_HERE';
BEGIN
  FOR MY_TABLE IN ALL_MY_TABLES 
  LOOP
    SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY 1) INTO TABLE_COLUMNS FROM USER_TAB_COLUMNS WHERE TABLE_NAME = MY_TABLE.TABLE_NAME;
    DELETE_STATEMENT := 'DELETE FROM ' || MY_TABLE.TABLE_NAME || ' WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM ' || MY_TABLE.TABLE_NAME || ' GROUP BY ' || TABLE_COLUMNS || ');';
    EXECUTE IMMEDIATE DELETE_STATEMENT;
    DBMS_OUTPUT.PUT_LINE(DELETE_STATEMENT);--print the statements we run
  END LOOP;
END;

answered Feb 21, 2017 at 10:18

Zsolt Z.

5998 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to delete duplicate rows from all of my oracle database tables, using PL/SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related