Can I specify tablespace when creating PK?

[elaine3839]

Got in a situation where I need to specify tablespace when creating the PK 'cause otherwise it will be create in SYSTEM tablespace which is bad. Is there anyway for me to do that?

Thanks a bunch!

[marist89]

sure:

marist89@lx920> create table xyz (id number(10),
  2  constraint xyz_pk primary key (id) using index tablespace users);

Table created.

Elapsed: 00:00:00.35

[jmodic]

Originally posted by elaine3839
Got in a situation where I need to specify tablespace when creating the PK 'cause otherwise it will be create in SYSTEM tablespace which is bad.

What realy is bad here is that your user has SYSTEM tablespace assigned as his/her default tablespace obviously!

[elaine3839]

I agree -- but isn't the user being assign to SYSTEM tablespace if you don't assign a default tablespace for the user during creation time? I thought the default tablespace should be USERS if you don't specify one, but my quick test didn't confirm that.

Originally posted by jmodic
What realy is bad here is that your user has SYSTEM tablespace assigned as his/her default tablespace obviously!

[elaine3839]

Excellent, thank you!

(I must made some syntax mistake when I tried it yesterday :-)

Originally posted by marist89
sure:

marist89@lx920> create table xyz (id number(10),
  2  constraint xyz_pk primary key (id) using index tablespace users);

Table created.

Elapsed: 00:00:00.35

[jmodic]

Originally posted by elaine3839
I agree -- but isn't the user being assign to SYSTEM tablespace if you don't assign a default tablespace for the user during creation time? I thought the default tablespace should be USERS if you don't specify one, but my quick test didn't confirm that.

Something that is defined as the default value must by definition exist in all circumstances, isn't it so? So it is more than logical that SYSTEM tablespace was chosen to be the default tablespace for all users. It most certanly can't be USERS or any other tablespace. Why? Because SYSTEM is the only tablespace that exists in any Oracle database! It can't be USERS or anything similar, because USERS tablespace might not even exist in the database.