Enabling constraint runs out of space..??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Enabling constraint runs out of space..??

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    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.

  2. #2
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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.......

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    an index is considered temporary segment until itīs rebuild 100%

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Yeah, I know, but why is it using the users default tablespace, rather than the table tablespace for the temp segments??

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thankyou.

  7. #7
    Join Date
    Jul 2002
    Posts
    335
    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
  •  



Click Here to Expand Forum to Full Width