Posted by This one came in via an AskTOM question. You need to be careful when disabling constraints that are underpinned by an index, especially when it comes to the storage for that index.
Let’s take a look at a simple example. I’ll explicitly nominate the LARGETS tablespace for my index that will support the primary key.
SQL> create table t as
2 select 1 x, 1 y from dual;
Table created.
SQL>
SQL> alter table t add constraint
2 t_pk primary key ( x ) using index tablespace largets;
Table altered.
SQL>
SQL> select tablespace_name from user_segments
2 where segment_name = 'T_PK';
TABLESPACE_NAME
--------------------
LARGETS
1 row selected.
SQL>
SQL> alter table t modify constraint t_pk disable;
Table altered.
SQL> select * from user_indexes where index_name = 'T_PK';
no rows selected
SQL> alter table t modify constraint t_pk enable;
Table altered.
Now let us look at where the index that is needed for that constraint has been re-created.
SQL> select tablespace_name from user_segments
2 where segment_name = 'T_PK';
TABLESPACE_NAME
--------------------
USERS
The index is a quite distinct entity from the constraint, and unless you nominate a tablespace it will be created in the default tablespace for the schema, which in my case was USERS.
So take care – you might need to pre-create indexes or explicitly rebuild them with a tablespace specification before re-enabling those constraints.
SQL> create table t as
2 select 1 x, 1 y from dual;
Table created.
SQL>
SQL> create unique index t_pk on t ( x ) tablespace largets;
Index created.
SQL> alter table t add constraint t_pk primary key ( x ) using index ;
Table altered.
SQL>
SQL> select tablespace_name from user_segments
2 where segment_name = 'T_PK';
TABLESPACE_NAME
--------------------
LARGETS
1 row selected.
SQL>
SQL> alter table t modify constraint t_pk disable;
Table altered.
SQL> select tablespace_name from user_segments
2 where segment_name = 'T_PK';
TABLESPACE_NAME
--------------------
LARGETS
1 row selected.
SQL> alter table t modify constraint t_pk enable;
Table altered.
SQL>
SQL> select tablespace_name from user_segments
2 where segment_name = 'T_PK';
TABLESPACE_NAME
--------------------
LARGETS
1 row selected.
Learning is not a spectator sport 
Connor -few questions.
which version was your test case is from?
I am on 12.2.0.1 – don’t see that happen.
demo@PDB1> col username format a10
demo@PDB1> select username,default_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE
———- ——————————
DEMO USERS
demo@PDB1> drop table t purge;
Table dropped.
demo@PDB1> create table t as
2 select *
3 from all_users;
Table created.
demo@PDB1> create unique index t_pk on t(user_id) tablespace ts_data;
Index created.
demo@PDB1> alter table t add constraint t_pk primary key(user_id) using index;
Table altered.
demo@PDB1> select tablespace_name from user_segments where segment_name =’T_PK’;
TABLESPACE_NAME
——————————
TS_DATA
demo@PDB1> select constraint_name,index_name
2 from user_constraints
3 where table_name =’T’
4 and constraint_type =’P’;
CONSTRAINT INDEX_NAME
———- ———-
T_PK T_PK
demo@PDB1> alter table t modify constraint t_pk disable;
Table altered.
demo@PDB1> select index_name from user_indexes where table_name =’T’;
INDEX_NAME
———-
T_PK
demo@PDB1> select tablespace_name from user_segments where segment_name =’T_PK’;
TABLESPACE_NAME
——————————
TS_DATA
demo@PDB1> select constraint_name,index_name
2 from user_constraints
3 where table_name =’T’
4 and constraint_type =’P’;
CONSTRAINT INDEX_NAME
———- ———-
T_PK
demo@PDB1> alter table t modify constraint t_pk enable;
Table altered.
demo@PDB1> select index_name from user_indexes where table_name =’T’;
INDEX_NAME
———-
T_PK
demo@PDB1> select tablespace_name from user_segments where segment_name =’T_PK’;
TABLESPACE_NAME
——————————
TS_DATA
demo@PDB1> select constraint_name,index_name
2 from user_constraints
3 where table_name =’T’
4 and constraint_type =’P’;
CONSTRAINT INDEX_NAME
———- ———-
T_PK T_PK
demo@PDB1> select tablespace_name from user_segments where segment_name =’T’;
TABLESPACE_NAME
——————————
USERS
demo@PDB1>
demo@PDB1>
See my reply to Ludith
Hello Connor,
Which database version have you used for this test ?
As far as I am aware, at least since 8i, when an index is created separately and then “picked up”
by adding a PK/UK constraint, the index remains in place when dropping or disabling the constraint.
I just tested this in LiveSQL, and, indeed, after disabling the constraint, the index is still there.
Cheers & Happy Easter !
Iudith Mentzel
LOL….this was a cut-paste fail on my part. Even in my conclusion I had “You might need to precreate the index” and then had cut-pasted a chunk of code from my “precreated index” solution into the main body. Thanks for spotting it – I’ve amended the post