Re: Why assign a size to a tablespace ?

In article <aak2.848302317_at_Isis.MsState.Edu> aak2_at_Ra.MsState.Edu (Atif Ahmad Khan) writes:
>
> I am just wondering why does oracle require that you assign
> a size to table/tablespace ? When you do it immediately
> takes up that much disk space. Why can't it be dynamic ?
>

 <SNIP>
> -------------
> insert into testdb
> *
> ERROR at line 1:
> ORA-01652: unable to extend temp segment by 315 in tablespace SYSTEM
> -------------
>
> So I do I just need to add space to tablespace SYSTEM ?
>
> Thanks.
>
> Atif Khan
> aak2_at_ra.msstate.edu

Oooh...I think you're going to hear from a lot of people, Atif!

First of all, you specify a size for a tablespace for a couple of good reasons. First, usually your Oracle tablespace is sharing the platform with other databases or other programs. You want to limit the footprint of the tablespace to avoid having it dynamicly stomp on your other systems. Second, since the tablespace equates to physica datafiles, specifying the full size up front prevents that datafile from becoming Fragmented as it grows to accomodate your needs.

But your REAL problem is that you're using the SYSTEM tablespace! Bad!! Oracle needs that tablespace to maintain the data dictionary, if you fill it up with a table cataloging your CD collections (as an example) you risk bringing the database to a grinding halt when the data dictionaries cannot extend! (See what I mean about using tablespaces to prevent different systems from stomping on each other?)

--
----------------------------------------------------------------
John P De Voy             "When amateurs dabble in databases  ()
DBA, NeXT Software Inc.    people get hurt!"                  /\