How to remove duplicate data in Oracle?

There are several methods to remove duplicate data in Oracle.

1. To remove duplicate rows using ROWID: first, identify duplicate rows using the ROWID function, then use the DELETE statement to remove these rows. Illustrated below is an example.

DELETE FROM table_name
WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column1, column2, ...);

1. Remove duplicate rows using the ROW_NUMBER() function: Add a sequence number to each row using the ROW_NUMBER() function, then delete rows with a sequence number greater than 1.示例如下：

DELETE FROM (
    SELECT column1, column2, ..., ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column1) AS rn
    FROM table_name
)
WHERE rn > 1;

1. Using temporary table to delete duplicate rows: First, create a temporary table, insert the data without duplicate rows into the temporary table, then delete the data from the original table, and finally reinsert the data from the temporary table back into the original table. Example is as follows:

CREATE TABLE temp_table AS SELECT DISTINCT * FROM table_name;

DELETE FROM table_name;

INSERT INTO table_name SELECT * FROM temp_table;

DROP TABLE temp_table;

The above are some common methods for removing duplicate data in Oracle, the specific choice of method depends on the specific situation and needs.