Q
Problem solve Get help with specific problems with your technologies, process and projects.

Setting a tablespace other than default

How can I set a tablespace other than the user's default tablespace for a primary key index while creating a table or alter a table by adding a primary key constraint? By default the index on a primary key gets created in the user's default space.

How can I set a tablespace other than the user's default tablespace for a primary key index while creating a table or alter a table by adding a primary key constraint? By default the index on a primary key gets created in the user's default space.
You can specify the tablespace that you want both the table and the primary key created in when you issue the create statement. In the example below, I have logged into SQL*Plus as the user scott whose default tablespace is USERS.
SQL> select default_tablespace from dba_users where username='SCOTT';

DEFAULT_TABLESPACE
------------------------------
USERS
Next, I want to create a table called dept2 with a primary key called dept2_pk. Note the tablespace clause for both the table and the primary key:
SQL> create table dept2
  2  (deptno NUMBER(2) constraint dept2_pk PRIMARY KEY
  3  USING INDEX tablespace example,
  4  dname VARCHAR2(20),
  5  loc VARCHAR2(20))
  6  TABLESPACE example;

Table created.
If I now select from USER_TABLES and USER_INDEXES, I see that both the table and primary key index have been created in a different tablespace from my default:
SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT2                          EXAMPLE
SALGRADE                       USERS
BONUS                          USERS
EMP                            USERS
DEPT                           USERS

SQL> select index_name, uniqueness, tablespace_name from user_indexes;

INDEX_NAME                     UNIQUENES TABLESPACE_NAME
------------------------------ --------- ------------------------------
DEPT2_PK                       UNIQUE    EXAMPLE
PK_EMP                         UNIQUE    USERS
PK_DEPT                        UNIQUE    USERS
The same process is followed for adding a primary key constraint to an existing table. In the example below, I add a primary key constraint to the existing DEPT2 table without specifying a tablespace (note that I drop the constraint first so that I can create the constraint):
SQL> alter table dept2 drop primary key;

Table altered.

SQL> alter table dept2 add constraint dept2_pk primary key (deptno);

Table altered.
You can see that it is created in the user's default tablespace, which in this case is USERS:
SQL> select index_name, tablespace_name from user_indexes;

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT2_PK                       USERS
PK_EMP                         USERS
PK_DEPT                        USERS
Now, I'll use the tablespace clause to create the primary key constraint:
SQL> alter table dept2 drop primary key;

Table altered.

SQL> alter table dept2 add constraint dept2_pk primary key (deptno)
  2  using index tablespace example;

Table altered.

SQL> select index_name, tablespace_name from user_indexes;

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT2_PK                       EXAMPLE
PK_EMP                         USERS
PK_DEPT                        USERS
You can see that it was created in a tablespace other than USERS.
This was last published in November 2005

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close