Home » RDBMS Server » Server Administration » create table with index in different tablespace
create table with index in different tablespace [message #186844] Wed, 09 August 2006 17:05 Go to next message
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

Re: create table with index in different tablespace [message #186845 is a reply to message #186844] Wed, 09 August 2006 17:18 Go to previous messageGo to next message
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.
Re: create table with index in different tablespace [message #186849 is a reply to message #186845] Wed, 09 August 2006 18:22 Go to previous messageGo to next message
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
Re: create table with index in different tablespace [message #186850 is a reply to message #186844] Wed, 09 August 2006 19:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: create table with index in different tablespace [message #186888 is a reply to message #186849] Thu, 10 August 2006 01:04 Go to previous messageGo to next message
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
Re: create table with index in different tablespace [message #186963 is a reply to message #186844] Thu, 10 August 2006 06:01 Go to previous message
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>' ;
Previous Topic: Accessing Renamed database using OEM IN oracle 10g
Next Topic: jserver problem
Goto Forum:
  


Current Time: Thu Dec 08 00:15:24 CST 2016

Total time taken to generate the page: 0.09739 seconds