Fwd: find duplicated records and

以下範例為找到有重複資料的方法

select *

from table

group by key1, key2, ..., keyN

having count (*) > 1;

--select * from owner.table_name group by column_name having count(*) >1

以下為按照每一列尋找 是否有重複的值

--find tables which doesn't have primary key or unique key 

spool count.sql

select 'select * from '||owner||'.'||table_name||'group by '||column_name||'having count(*) > 1;' from all_tab_columns

where table_name in (select distinct table_name from all_constraints where constraint_type not in  ('P','U') and owner in (''));

spool off

spool count.log

start count.sql

spool off

2.

ALTER TABLE sales ADD CONSTRAINT sales_unique
UNIQUE(sales_id);