Rajesh,

Either name the tablespace in your index creation script or
alter the user assigning the index tablespace as the default. Create all
your indexes and then alter the user again re-assigning the previous
default tablespace.

Alter user myuser default tablespace my_indx;

Create index.......

Alter user myuser default tablespace my_tab;

Any time in the future you want to create an index, you will have to do
this again. I find it much easier to name the tablespace when creating
tables and indexes than trying to move things around later.

IJCA,
Gil Bruce

-----Original Message-----
From: Rajesh kalaria
[mailto:oracledba-ezmlmshield-x81985712.[Email address protected]
Sent: Wednesday, November 28, 2007 7:34 AM
To: LazyDBA Discussion
Subject: How to associate index tablespace with a user

All,

I am executing following commands in following sequence :

CREATE TABLESPACE MY_INDX DATAFILE '/MY_INDX.dbf' SIZE 512M
AUTOEXTEND ON NEXT 64M DEFAULT STORAGE(INITIAL 128K NEXT 1M PCTINCREASE
10 MINEXTENTS 1 MAXEXTENTS UNLIMITED);

CREATE TABLESPACE MY_TAB DATAFILE 'MY_TAB.dbf' SIZE 512M AUTOEXTEND
ON NEXT 64M DEFAULT STORAGE(INITIAL 128K NEXT 1M PCTINCREASE 10
MINEXTENTS 1 MAXEXTENTS UNLIMITED);

CREATE USER MYUSER IDENTIFIED BY MYUSER DEFAULT TABLESPACE MY_TAB
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON MY_INDX QUOTA UNLIMITED ON
MY_TAB;

But I am seeing that when I am creating a index without
specifying the tablespace, it is by default taking the MY_TAB
tablespace. But I want that it should take the MY_INDX tablespace which
I have created explicitly to store indexes in it.

I can specify the tablespace when creating the index, and that
will resolve the problem. But the problem is that I want to assign
MY_INDX tablespace for storing indexes for user MYUSER. So how can I
achieve this ?

Regards,
Rajesh


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


Oracle LazyDBA home page