Re: Altering Indexes

Mark Leith wrote:
>
> Just a question off the wall here - kind of related:
>
> Does anybody know why Oracle does NOT give the option to have a DEFAULT
> INDEX TABLESPACE along with TEMPORARY and DEFAULT (for Tables for example)..
> I know, I know it's just another thing to add to your syntax - but it would
> be a FAR better way of doing things wouldn't it?
>
> I would much prefer to say:
>
> create user mark identified by beer
> default table tablespace USER_DATA
> quota 100 M on USER_DATA
> default index tablespace USER_IDXS
> quota 100 M on USER_IDXS
> temporary tablespace TEMP;
>
> as this totally takes away the nightmare of having them all created in one
> single tablespace..
>
> Anyone with an "in" with Oracle know the answer? Anyone care to speculate?
> :>
>
> Cheers
>
> Mark
>

Mark,

   Good suggestion. As far as one usually finds more indices in the data tablespace than the reverse, I have flirted with the idea of making the tablespace devoted to indices the default one, but it's changing the problem. But I have something to suggest :  

 create user mark identified by beer
 default table tablespace SYSTEM
 quota 100 M on USER_DATA
 quota 100 M on USER_IDXS
 temporary tablespace TEMP;

 (assuming of course that you do not have the UNLIMITED TABLESPACE privilege). My bet is that it won't take long before you remember to always specify the tablespace, yek, yek, yek. In case using SYSTEM would make you (understandably) uncomfortable, you can create say a 50K BARELAND tablespace on which nobody has quotas.

-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).