specifying index tablespace while enabling disabling primary key constraints
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: specifying index tablespace while enabling disabling primary key constraints

  1. #1
    Join Date
    Feb 2009
    Posts
    1

    specifying index tablespace while enabling disabling primary key constraints

    Hi
    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by chickentots View Post
    ... is there any way to specify the tablespace in which the primary key index is to be rebuilt *WHILE RE-ENABLING THE CONSTRAINT*
    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.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width