Thursday, January 20, 2011

How to ensure that the index for a constraint does not go in your default tablespace

This is a simple example, which I did for a colleague several years ago. He wanted to ensure that the index for a constraint he was creating did not go in his default tablespace (I cannot remember why he wanted to do this). I don't think it needs any more explanation than that:

SQL> alter table emp disable constraint pk;

Table altered.

SQL> alter table emp enable constraint pk
  2  using index tablespace appl_data02;

Table altered.

SQL> select tablespace_name from user_segments
  2  where segment_name = 'PK';

TABLESPACE_NAME
------------------------------
APPL_DATA02

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
APPL_DATA01

SQL>

No comments: