<TT>
-- this example works in oracle 8.0.5
-- create table "exceptions"
drop table exceptions;
@e:\orant\rdbms80\admin\utlexcpt.sql
-- create table test
drop table test;
create table test (
col1 number(10),
col2 number(10),
col3 varchar2(5)
);
-- insert rows
insert into test values ( 1, 1, 'AA' );
insert into test values ( 1, 2, 'AB' );
insert into test values ( 2, 1, 'BA' );
insert into test values ( 2, 1, 'BA' );
insert into test values ( 3, 1, 'CA' );
insert into test values ( 3, 2, 'CB' );
insert into test values ( 3, 2, 'CX' );
insert into test values ( 4, 1, 'DA' );
insert into test values ( 4, 2, 'DB' );
insert into test values ( 4, 3, 'DC' );
commit;
-- first attempt to add primary key on (col1,col2)
alter table test
add primary key (col1,col2)
exceptions into exceptions;
-- to view the bad rows
select * from test
where rowid in
(select row_id from exceptions);
-- to remove some rows that the primary key could be enabled
delete from test
where rowid in
(select min(rowid) from test
group by col1, col2
having count(*) > 1);
alter table test
add primary key (col1,col2)
exceptions into exceptions;
</TT>