How to locate duplicate rows without having to name every column in table?

Question

Due to flawed design, a table was created without a unique identifier for each row. Due to another issue, a number of those rows have been completely duplicated. I figured out how to determine the duplicated rows, but I had to include every column name in the group by clause. Is there a way for me to determine those full duplicates without naming every column in the group by? For this table it's not a huge deal, as there are only 20 or so columns, but if I had a 50 column table, etc., it could get a bit onerous.

I'm on Oracle 19c, just FYI, and I have no way of upgrading. My end goal will be to delete the duplicate records by referencing the rowid, but for now I'm focusing on improving this query.

Below is basically the query I used and for which I am seeking improvement:

select col1, col2,...col20
from table
where col3 in ('x','y')
group by col1, col2...col20
having count(*) > 1;

Answer 1

From Oracle 21, you can use MINUS ALL:

SELECT * FROM table_name
MINUS ALL
SELECT DISTINCT * FROM table_name

Which, for the sample data:

CREATE TABLE table_name (col1, col2, col3) AS
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 1, 2 FROM DUAL UNION ALL
SELECT 1, 2, 3 FROM DUAL UNION ALL
SELECT 1, 2, 3 FROM DUAL UNION ALL
SELECT 1, 2, 3 FROM DUAL;

Outputs only the duplicated rows:

COL1	COL2	COL3
1	1	1
1	2	3
1	2	3

fiddle