Re: Tablespaces structure question!

I had the same problem with a database that I took over. One table per tablespace. Also one index per tablespace. Got to be quite a nightmare administering the tablespaces. Having one table per tablespace does let you easily move tables among volumes. Just take the tablespace offline, move the tablespace's datafile to the new location, rename the datafile in the database, and bring the tablespace online. Easy to move tables without export/import! But other administration tasks can become daunting. For example, when trying to trend tablespace growth you have to look at a lot of tablespaces rather than just a few. So there are pros and cons, but most people would probably say that this is not a good thing.

As far as your suggestion on the three tablespaces... I can't say that it's wrong (or right). It all depends on your situation. Personally, I'd rather put related data into one tablespace. Or use the tablespaces to seperate your data based on volume of usage. There are other methodologies as well.

HTH,
Brian

ttrivedi wrote:
>
> 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
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================