Re: Primary keys in a different tablespace than the tables

Annie TANGUY <tanguy_at_concept.fr> writes:

>As all the books written about tuning oracle installation refers to
>separate indexes and tables, i did try to separate them.
>but, primary keys aren't indexes so they stick to the tables via
>exp/imp.
 

>Is there a way to force them out of the tables tablespace ?

alter table XXX disable primary key;
alter table XXX enable primary key using index <index storage params>;

Ex:
alter table emp disable primary key;
alter table emp enable primary key using index tablespace INDEXES;

Note - this won't work if you have FK relationships relying on the key (won't be able to disable in the first place). What I do is create a SQL script that dynamically queries dba_constraints and disables all referencing foreign keys of the index, disables and re-enables the PK, and then re-enables all FKs again.

Do this in off hours, and all in one fell swoop, and you'll have few problems. You obviously don't want to disable your PKs and FKs while the DB is open for end-user transactions!

-- 
/(o\ Nathan D. Hughes
\o)/ nhughes_at_umich.edu