This happens to be an active database - not a data warehouse. Do you know
if we will have a problem in that case? What kind of lock is held during a
primary key creation?
Thanks,
Michael
On Mon, Jun 27, 2016 at 2:27 PM, Adric Norris <landstander668@xxxxxxxxx>
wrote:
I've used this approach a number of times, including on multi-TB tables,
and it's worked very very well. I usually validate the constraint once the
missing data has been fully populated, which is a non-blocking operation
(since Oracle already knows that no new data can be inserted which violates
the constraint in ENABLE NOVALIDATE mode), but this step may not be
required for a data warehouse scenario.
alter table dba6975_test modify constraint dba6975_test_CPK validate;
On Mon, Jun 27, 2016 at 1:44 PM, Michael Cunningham <
napacunningham@xxxxxxxxx> wrote:
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
--
"In the beginning the Universe was created. This has made a lot of people
very angry and been widely regarded as a bad move." -Douglas Adams
