specifying index tablespace while enabling disabling primary key constraints
I am using an ETL process to load an Oracle 10g D/B.
These processes are largely immutable and have SQL code generators to enable / disable constraints.
Note that the process does not allow one to drop and recreate the constraints.
Nor does it allow to customize the code generator to use a specific index while enabling constraint.
With this background, is there any way to specify the tablespace in which the primary key index is to be rebuilt *WHILE RE-ENABLING THE CONSTRAINT*
Note: there is no other option other than to disable / enable the constraint.
Since the default tablespace is different from the tablespace in which the objects reside, the index is being rebuilt in a diff tablespace which violates one of the requirements of our system. The default tablespace cannot be changed
When you enable/disable a constraint you are not rebuilding the underlying index, you are just telling Oracle you can't care less about the constraint then you tell Oracle to start checking it again.
Originally Posted by chickentots
Having said that and since base index is never rebuilt you cannot move it.
On the other hand, if you want a particular index to be sitting in a different tablespace just move it during a maintenance window; index will stay there.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width