Enabling constraint runs out of space..??

[Horace]

I have a table in a dedicated tablespace

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'N_CONTACT_HISTORY';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
N_CONTACT_HISTORY              MART

The table has a primary key constraint on it

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'N_CONTACT_HISTORY';

CONSTRAINT_NAME
------------------------------

PK_N_CONTACT_HISTORY

The table owner has a different tablespace as default

SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS; (output edited)

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------

N_OWNER                        USERS

Here's the problem.........

ALTER TABLE N_CONTACT_HISTORY ENABLE VALIDATE CONSTRAINT PK_C_CONTACT_HISTORY;

ORA-01652: unable to extend temp segment by 64 in tablespace USERS

If the table is in tablespace MART, why is the constraint enablement using USERS??

I think I'll go RTFM whilst someone comes up with a good answer. Thanks.

[Horace]

Oh, by the way, I know that constraint enablement will rebuild the underlying primary key associated index, and this will initially be done in a temp segment. But I would expect this to happen in the tablespace that the table is in.......

[pando]

an index is considered temporary segment until it´s rebuild 100%

[Horace]

Yeah, I know, but why is it using the users default tablespace, rather than the table tablespace for the temp segments??

[jmodic]

When you disable a PK constraint, by default its underlying index is dropped. The definition of constraint remains in a dictionary, while the definition of its index is wiped out, Oracle no longer knows in which tablespace the index under that PK was created. So after you enable that PK again, Oracle has to create a fresh new index for it. In which tablespace will that index be created? Well, as allways, if you don't specify that explicitely, it will be created in user's default tablespace, regardles of where it's parent table resides.

So if you don't want that index to be in the default tablespace, tell to Oracle where you want it:

ALTER TABLE n_contact_history
ENABLE VALIDATE CONSTRAINT pk_c_contact_history
USING INDEX TABESPACE mart;

[Horace]

Thankyou.

[bazza]

Originally posted by jmodic
When you disable a PK constraint, by default its underlying index is dropped. The definition of constraint remains in a dictionary, while the definition of its index is wiped out, Oracle no longer knows in which tablespace the index under that PK was created. So after you enable that PK again, Oracle has to create a fresh new index for it. In which tablespace will that index be created? Well, as allways, if you don't specify that explicitely, it will be created in user's default tablespace, regardles of where it's parent table resides.

So if you don't want that index to be in the default tablespace, tell to Oracle where you want it:

ALTER TABLE n_contact_history
ENABLE VALIDATE CONSTRAINT pk_c_contact_history
USING INDEX TABESPACE mart;

Ah, I learn something new every day...

Bazza