-
Enabling constraint runs out of space..??
I have a table in a dedicated tablespace
Code:
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
Code:
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
Code:
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS; (output edited)
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
N_OWNER USERS
Here's the problem.........
Code:
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.
-
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.......
-
an index is considered temporary segment until it´s rebuild 100%
-
Yeah, I know, but why is it using the users default tablespace, rather than the table tablespace for the temp segments??
-
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:
Code:
ALTER TABLE n_contact_history
ENABLE VALIDATE CONSTRAINT pk_c_contact_history
USING INDEX TABESPACE mart;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
-
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:
Code:
ALTER TABLE n_contact_history
ENABLE VALIDATE CONSTRAINT pk_c_contact_history
USING INDEX TABESPACE mart;
Ah, I learn something new every day...
Bazza
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|