Re: schemas & tablespaces

I hate to be cruel, but don't try and compare apples with oranges. A tablespace in Oracle is definitely NOT a database, though you get plenty of respectable people behaving as if they were.

For pure performance, and money being no object, then each database should be housed on its own server. Each database will consists of at least 6 tablespaces -SYSTEM, ROLLBACKS, TEMP, DATA, INDEXES and "OTHER". Each basic tablespace thus described can be sub-divided: LARGE SLOW DATA; LARGE FAST DATA; SMALL SLOW DATA; SMALL FAST DATA and so on.

The point being that tablespaces should contain segments which behave and grow in the same way. That means tablespaces should bear absolutely NO relation to what most might consider a database (or, possibly, an application): STATES_OF_AUSTRALIA hasn't changed in 100 years, and wouldn't be likely to do so whether we were talking a SALES database/application or a Human Resources database/application: in either case, it should go into the SMALL SLOW tablespace.

You can define a schema as being simply "the collection of objects owned by a User", and that clearly has zilch to do with an application or database either. Peter, being my best Developer, has created tables in both SALES and HR databases: that means he has a schema in both databases, because he owns objects in both.

At the end of the day, do try and stop this comparison between products. Both SQL Server and Oracle work in essentially the same way, and both have their role to play: but if you approach one just by trying to map the features of the other onto it, you are going to head into a deep abyss of sub-optimal organisation. Explot the features of both to their best advantage -and that requires you to understand the features of both on their own terms.

Regards
HJR <alvie_at_my-deja.com> wrote in message news:943575$12s$1_at_nnrp1.deja.com...
> Hi group
> I'm coming from a MS SQL Server background and want to know what the
> best method for creating new schemas is, as far as tablespaces are
> concerned. Is it better to create a new tablespace for each new
> schema? I'm equating schemas with SQL Server databases. Each schema
> will be application specific. If not, what is the recommended approach?
>
> thanks
> -as
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Wed Jan 17 2001 - 05:47:45 CST