Assuming you're running 11g -
If you do 'create index online' you'll take a mode 2 lock on the table to build
the index, then (briefly, probably) a mode 4 (waiting for DML on the table to
commit and block new DML locks) to bring the index up to date with any changes
made during the build. This MAY take some time (a) to get the lock and (b) to
bring the index up to date.
Adding the PK should take no time in novalidate mode, but won't add the NOT
NULL declaration to the column
altering the PK to validate will lock the table in mode 2 (no interference with
DML) but will do a tablescan to check for NOT NULL, and then modify the column
to NOT NULL.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Michael Cunningham [napacunningham@xxxxxxxxx]
Sent: 27 June 2016 19:44
To: oracle-l@freelists org
Subject: Question about unique constraint
Posting this for a colleague.
Hello everyone.
I have various tables of fairly large sizes from 100GB till 3TB that I need
to add primary key constraint in such way that clients can continue modifying
tables. Locking tables is not an option. One of the approach I am considering
is to build unique index online first then add constraints using index
novalidate. Will this approach work? Are there side effects of adding
constraints using novalidate option.
CREATE UNIQUE INDEX dba6975_test_PK ON dba6975_test(test_id) LOGGING TABLESPACE
INDXTBS1 ONLINE;
alter table dba6975_test ADD CONSTRAINT dba6975_test_CPK primary key(test_id)
using index dba6975_test_IDX1 novalidate
Suggestions are greatly appreciated.
--
Michael Cunningham