OraFAQ Forum: Server Administration » create table with index in different tablespace

Home » RDBMS Server » Server Administration » create table with index in different tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

create table with index in different tablespace [message #186844]

Wed, 09 August 2006 17:05

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

how can i create a table with its index in a different tablespace? Is it possible?

What should i need to consider??

What is the right syntax??

Thank you very much...
Alex

Report message to a moderator

Re: create table with index in different tablespace [message #186845 is a reply to message #186844]

Wed, 09 August 2006 17:18

ebrian
Messages: 2794
Registered: April 2006

Senior Member

SQL> create table test1 (a1 number, a2 varchar2(20)
  2  constraint a1_pk primary key using index tablespace users_idx);

Table created.

Report message to a moderator

Re: create table with index in different tablespace [message #186849 is a reply to message #186845]

Wed, 09 August 2006 18:22

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

the foreign keys create index too?? or only the primary keys??

In your syntax the table is created in users_idx tablespace too, or where do i specify the tablespace for the table?

thank you very much?
Alex

Report message to a moderator

Re: create table with index in different tablespace [message #186850 is a reply to message #186844]

Wed, 09 August 2006 19:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I suggest that you take a few moments to read the fine SQL Reference Manual found at http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
All your questions will be answered if you are capable & willing to read the fine manual.

Report message to a moderator

Re: create table with index in different tablespace [message #186888 is a reply to message #186849]

Thu, 10 August 2006 01:04

ninja
Messages: 6
Registered: August 2006
Location: INDIA

Junior Member

dear alex,

try this

SQL> create table test1 (a1 number, a2 varchar2(20)
2 constraint a1_pk primary key using index tablespace users_idx) tablespace my_TBS;

Table created.

this will create index in user_idx tbs and table in my_TBS

sincerely

hardik

Report message to a moderator

Re: create table with index in different tablespace [message #186963 is a reply to message #186844]

Thu, 10 August 2006 06:01

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Also ,
Omitting any of the tablespace name will cause the object(Table or Index) to be created in your default tableaspce .

Default tablespace:
select default_tablespace from dba_users
where username='<Your username>' ;

Report message to a moderator

Previous Topic:	Accessing Renamed database using OEM IN oracle 10g
Next Topic:	jserver problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:25:38 CDT 2024