编写一个将为您编写查询的查询。
例如，“john smith”在这里是重复的：

SQL> select * from my_data order by 1;

FULL_NAME  FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
h gonzalez h                    gonzalez
john smith john                 smith
john smith john                 smith
rudy chan  rudy                 chan

Query使用user_tab_columns并聚合所有列名，将它们连接到select语句的其余部分：

SQL>   SELECT    'select '
  2           || LISTAGG (column_name, ', ') WITHIN GROUP (ORDER BY column_id)
  3           || ', count(*) cnt '
  4           || CHR (10)
  5           || '  from '
  6           || table_name
  7           || CHR (10)
  8           || '  group by '
  9           || LISTAGG (column_name, ', ') WITHIN GROUP (ORDER BY column_id)
 10           || CHR (10)
 11           || '  having count(*) > 1;' statement_to_run
 12      FROM user_tab_columns
 13     WHERE table_name = 'MY_DATA'
 14  GROUP BY table_name;

STATEMENT_TO_RUN
--------------------------------------------------------------------------------
select FULL_NAME, FIRST_NAME, LAST_NAME, count(*) cnt
  from MY_DATA
  group by FULL_NAME, FIRST_NAME, LAST_NAME
  having count(*) > 1;

现在，复制/粘贴上面的statement_to_run并得到结果：

SQL> select FULL_NAME, FIRST_NAME, LAST_NAME, count(*) cnt
  2   from MY_DATA group by
  3  FULL_NAME, FIRST_NAME, LAST_NAME having count(*) > 1;

FULL_NAME  FIRST_NAME           LAST_NAME                   CNT
---------- -------------------- -------------------- ----------
john smith john                 smith                         2

SQL>

把所有的栏都写出来。
有几十列，大约30列，名字看起来像：'AGtrf-456 F_R值'
复制粘贴。
在SQL*Plus中，您可以使用DESCRIBE命令来描述表，并且可以从表描述中复制列名。
或者，您可以使用以下命令列出所有列：

SELECT '"' || column_name || '",'
FROM   user_tab_columns
WHERE  table_name = 'MY_DATA'
ORDER BY column_id;

然后将查询的输出复制粘贴到SELECT和GROUP BY子句中。
可以自动生成查询。
是的，但通常不值得这样做，因为编写查询以生成查询所需的时间比仅列出列和复制粘贴所需的时间要长。
如果有许多列名需要使用带引号的标识符（即，它们大小写混合或使用非标准字符，如-），则可以用途：

SELECT EMPTY_CLOB()
       || 'SELECT '
       || LISTAGG('"' || column_name || '"', ',') WITHIN GROUP (ORDER BY column_id)
       || ', COUNT(1) FROM MY_DATA GROUP BY '
       || LISTAGG('"' || column_name || '"', ',') WITHIN GROUP (ORDER BY column_id)
       || ' HAVING COUNT(1) > 1;'
FROM   user_tab_columns
WHERE  table_name = 'MY_DATA'
ORDER BY column_id;

除非列太多且LISTAGG超过4000个字符，否则您需要使用以下代码：

WITH columns (col, pos) AS (
  SELECT '"' || column_name || '",',
        column_id
  FROM   user_tab_columns
  WHERE  table_name = 'MY_DATA'
  ORDER BY column_id
)
SELECT sql
FROM   (
  SELECT 'SELECT ' AS sql, 0 FROM DUAL
  UNION ALL
  SELECT col, pos FROM columns
  UNION ALL
  SELECT ' COUNT(1) FROM MY_DATA GROUP BY ', 10000 FROM DUAL
  UNION ALL
  SELECT col, 10000 + pos FROM columns
  UNION ALL
  SELECT '1 HAVING COUNT(1) > 1', 20000 FROM DUAL
  ORDER BY 2
)

fiddle