If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.......

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:

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: