Help with enabling PK / Rollback segment problems

[lcapece]

I am having a problem with enabling a primary key constraint on a large table:
Table has apprx 100 Million rows, the PK is disabled due to the fact that there are about 1 Million dupe rows. My problem is when I enable the PK using the EXECEPTIONS into EXCEPTIONS clause, the rollback rollback segment usage can not seem to be controlled. The routine will fail due to the usual snapshot to old.
My first statement in the SQL is SET TRANSACTION USE ROLLBACK SEGMENT RB_LARGE {RB_LARGE is 3GB in size). The internal exceptions handler seems to ignore the explict use of RB_LARGE and will use a random smaller RBS. Is there anything you can recommend, other than off-lining the small RBS's before running the SQL?

Any help is much appreciated!

 

[karluk]

I don't know why you can't assign a rollback segment. Perhaps it's because the statement you are executing is DDL, rather than DML.

The idea I like the best as a work around is yours - put all the other rollback segments offline while doing this. If this isn't feasible, here are c couple of other ideas.

Create a new table that contains the primary key column(s) of your large table. Copy a (small) subset of the rows into the new table. Identify the exceptions for the subset. Fix the exceptions in the large table. Repeat as often as needed to eliminate duplicates.

Create a nonunique index on your primary key column(s). The advantage of doing this is that you will probably be able to identify duplicates very efficiently by doing an index scan. Fix the duplicates, drop the nonunique index, and create the primary key. The sql for identifying duplicates would be something like the following:

CREATE TABLE PK_DUPS AS
(
SELECT PK_COL1, PK_COL2 FROM LARGE TABLE
GROUP BY PK_COL1, PK_COL2
HAVING COUNT(*) > 1
)

If necessary, you could use this technique on subsets of the data.