Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: default index tablespace?

Re: default index tablespace?

From: David Fitzjarrell <oratune_at_aol.com>
Date: Thu, 28 Dec 2000 20:30:14 GMT
Message-ID: <92g7sn$sqr$1@nnrp1.deja.com>

In our last gripping episode wutang_warrior_at_my-deja.com wrote:
> So wouldn't it be a good idea to have the ability to assign a default
> index tablespace to users?
>
> In article <3a4af958_at_news.iprimus.com.au>,
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
> > Comments below.
> >
> > <wutang_warrior_at_my-deja.com> wrote in message
> > news:92eclj$fmn$1_at_nnrp1.deja.com...
> > > Must I specify the tablespace for my indexes with all my DDL?
> > > isn't there a default_index_tablespace for dba_users?
> > >
> >
> > No. You set a 'default tablespace' for all Users (including SYS and
> > SYSTEM). That's the tablespace in which segments will be stored
 unless the
> > relevant "create" statement explicitly states otherwise. In this
 regard,
> > Oracle makes no distinction between table segments and index
 segments.
> > They're both segments, and both would end up in the same default
 tablespace.
> >
> > However, it's just sloppy practice not to explicitly state the
 required
> > tablespace at create time, so it shouldn't be an issue.
> >
> > > What about the different names given to the Index tablespace
 between
> > > Oracle versions?
> >
> > Who cares? Oracle certainly doesn't. ALL tablespaces, bar SYSTEM,
 have
> > names which are at the whim and mercy of the relevant DBA. You can
 call
> > your data tablespace "DATA", "DATA01", "USERS" or "HAPPYNEWYEAR" -
 Oracle
> > won't mind one way or another. And the same goes for the Index
 tablespace.
> > So the names different DBAs use has precisely zilch to do with the
 version
> > you are using.
> >
> > >can I just change it to whatever I like when creating
> > > my DB or do catalog.sql, catproc.sql, etc... rely on it?
> >
> > See above. No they don't. Catalog builds the data dictionary
 views -
 it's
> > only concerned with finding a tablespace called SYSTEM. Catproc is
> > concerned with enabling PL/SQL functionality and building certain
 key
> > packages -which also reside in SYSTEM.
> >
> > >I suppose
> > > their indexes just sit in the SYSTEM tablespace....
> > >
> >
> > They certainly do.
> >
> > HJR
> >
> > >
> > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

No. Laziness has no place in database management, and that includes object creation. If one were to have the ability to assign a 'default index tablespace' to a user that would require Oracle to know the difference between an index segment and a table segment which would be a far more complex issue than you realise. Not having such a default requires the user to think about the objects he or she is creating and where best to place them, seeking guidance when necessary. The default tablespace is to keep errant objects out of the SYSTEM tablespace. Users can create 'temporary' tables [by this I mean the tables created are not composed of temporary segments, but are transient, used for but a single purpose and then dropped], most often with the 'create table ... as select ...' syntax. This syntax is a but a form of the full CREATE TABLE statement, and, as such, accepts the TABLESPACE specifier as well as STORAGE parameters although they do occur BEFORE the select clause:

create table mytable
tablespace mytabspace
storage( initial 10k next 10k pctincrease 0) as select mycol1, mycol2, mycol3
from myoldtable;

This can sometimes be confusing since, with most CREATE statements where objects are concerned, the TABLESPACE and STORAGE parameters appear at the end. As such the TABLESPACE and STORAGE parameters are usually discarded and the default tablespace assigned to the user is the final location of the newly created object.

To increase the complexity of the database kernel by instituting a 'default index tablespace' would require far more work by the Oracle development team, and result in far more work by the Oracle kernel, than any benefit that may be derived from the implementation. The users should learn to properly create database objects, period. Failing that, the users will be stuck with the location so conveniently provided by the default tablespace assigned to them. Hopefully that is NOT SYSTEM.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/
Received on Thu Dec 28 2000 - 14:30:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US