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

Oracle LazyDBA home page