Home » RDBMS Server » Server Administration » Tablespace name for constraints
Tablespace name for constraints [message #133433] Fri, 19 August 2005 01:42 Go to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

Hi,
Could you please tell me where can I find the tablespace name for a given constraint.

When I tried to generate the script for table from TOAD I could see the tablespace detail in the create constraint script and it is different from the tablespace for table.

Please help me.

Thanks in advance.

Regards
Raja
Re: Tablespace name for constraints [message #133497 is a reply to message #133433] Fri, 19 August 2005 06:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
a certain constraints (Primary / Unique ) when created, oracle will create an index on behalf of the constraint.
That index takes some storage and may or may not need to be in the same tablespace.

  1* alter table emp add constraint pk1 primary key (empno) using index tablespace tools
scott@9i > /

Table altered.

scott@9i > select index_name,table_name,tablespace_name from user_indexes where table_name='EMP';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PK1                            EMP                            TOOLS

Re: Tablespace name for constraints [message #133544 is a reply to message #133497] Fri, 19 August 2005 09:37 Go to previous message
anujlal
Messages: 4
Registered: August 2005
Junior Member
Here is whatever little I know on this topic:

I suppose when the default tablespace for your user is set
(by default it is system if not specified in create user statement), then all objects, whether tables or indexes will be created in that tablespace. But this will be true only when you are logged in to the schema in which you have to create the object.


Anyway, best thing is to specify the tablespace while creating tables/indexes/constraints.

Regards
Anuj
Previous Topic: sql statement logs
Next Topic: Export Error
Goto Forum:
  


Current Time: Tue Feb 04 00:20:51 CST 2025