Re: Tablespaces structure question!

ttrivedi wrote:
>
> Sybrand Bakker wrote:
>
> > ttrivedi <ttrivedi_at_deja.com> schreef in berichtnieuws
> > 3904AF70.B5F65BCE_at_deja.com...
> > > Hey Gurus,
> > >
> > > Hi. I have a question which , if answered, might help me clear off some
> > > mud in my head.
> > > At my present client we have a tablespace structure which comprises of
> > > at present
> > > each table in its own tablespace. Yes I typed it correctly each table in
> > > its own tablespace.
> > > What problems ,if any ,do you guys foresee for this type of
> > > architecture. One of the suggestions
> > > that we have is to have a tablespace structure which elevates the tables
> > > as and when they grow.
> > > Like we have three tablespaces for the application which are small_tbs,
> > > medium_tbs and large_tbs.
> > > When the table grows beyond a certain point we move it to the medium and
> > > ultimately the large_tbs.
> > > What do you guys think of the same ?
> > >
> > > Any questions, comments, outbursts , epiphanies would be welcome.
> > >
> > >
> > >
> > > Tapan
> > >
> >
> > each table in its own tablespace. Yes I typed it correctly each table in
> > > its own tablespace.
> > > What problems ,if any ,do you guys foresee for this type of
> > > architecture.
> >
> > You'll run out of datafiles, there's a maximum you or someone else specified
> > at database creation usually 254. This means you can have about 254 tables
> > :)
> > For the least space management issues the idea of having three different
> > type of tablespaces seems to work good, provided you don't specify storage
> > clauses on object level. Everything is handled by the default storage clause
> > of the tablespace.
> > Technet.oracle.com should have a white paper called 'Space: the final
> > frontier'.
> >
> > Hth,
> > Sybrand Bakker, Oracle DBA
>
> Thanks a lot Sybrand. It does help.
>
> Tapan

Have a read up on locally managed tablespaces - it solves a lot of space management dramas with little effort...

The one table per tablespace probably comes from an ex-DB2 person - this technique was common in earlier versions of DB2.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse