Re: schemas & tablespaces

Howard -
thanks a lot. This info is really helpful. I agree with one db per application. Likewise, I fully grasp the user/schema concepts now.

thanks again
-as

> <alvie_at_my-deja.com> wrote in message

 news:944k1c$60k$1_at_nnrp1.deja.com...
> > Thanks. This info is really helpful. From the description below -
> > then do all schemas on a particular db server access the same
> > tablespaces (the one's described below)?
>
> No. Well, not necessarily! A schema is a collection of objects
 owned by
> one User. Hence, the EMP table, created and owned by SCOTT belongs in
> Scott's schema. But the index on the EMP table is also owned by
 Scott. So
> the Index is in his schema too. But the EMP table will reside in the
 DATA
> tablespace, and (if Scott's got any sense) the Index will reside in
 the
> INDEX tablespace. So one schema, multiple objects, multiple
 tablespaces.
>
> Incidentally, I saw you post elsewhere whether a schema is the same
 thing as
> a User, or rather if there is a one to one corrrespondence between
 them.
> The answer is "yes". Since a schema is a colleciton of objects owned
 by a
> User, Scott's objects by definition reside in the Scott schema. If
 Scott
> wanted to create a new object in a different schema, he could only do
 so by,
> for example, issuing the command "create table mary.blah" -and the
 BLAH
> table now resides in Mary's schema. But Mary is simply another User
 in the
> data dictionary -so it remains true -one User, one Schema.
>
> Having said that, just because your data dictionary has entries for
 1000
> Users doesn't mean you'll have 1000s schemas -99% of those Users will
 almost
> certainly not have the rights to create tables or indexes, and
 accordingly,
> without objects created in their name, there is no schema for them.
>
> > For example, if I had two
> > different applications that used the same server and seperate
 schemas
> > for each application, would both schemas use the DATA tablespace for
> > the table data and the INDEXES tablespaces for their indexes?
>
> They could do so, and the only reason why you wouldn't do it like
 that is if
> the i/o patterns for the two applications conflicted. If you tend to
 do
> mass updates in one whilst the other is also being subject to huge
 data
> loads, for example, you'd be crazy to house both sets of tables in
 the one
> DATA tablespace, because the conflicting i/o would mean both were
 knackered.
> Have a SALESDATA and a PURCHASESDATA tablespace, put the relevant
 datafiles
> on separate hard disks, and the problem would be minimised. However,
 my
> strong recommendation is one database per application because
 regardless of
> conflicting physical i/o, your two applications will still be
 fighting for
> resources in the Instance.
>
> Still, if your two applications DON'T conflict, by all means stick
 their
> tables all in the one tablespace. And yes, at that point, the only
 way to
> tell that the SALES table belongs to the SELLING application, but the
 BUYS
> belongs to PURCHASING is that the full names for the tables will be
> SELL.SALES, and BUYS.PURCHASE. That's actually the proper naming
 convention
> for any Oracle table -schema.objectname. You can only get away with
 not
> using the schema identifier if you are logged on as the schema
 owner. For
> example, log on as SYSTEM, and you must always refer to SCOTT.EMP.
 Log on
> as Scott himself, and you can now happily do 'select * from emp'.
>
> (You can use synonyms to make it look as though you are referencing a
 table
> without a schema identifier, but that just means Oracle is resolving
 the
> real schema.object name for you behind the scenes).
>
> HTH
> Regards
> HJR
>
> If so,
> > how do you distinguish between the two. By schema.object notation?
> >
> >
> >
> > 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.
> >
> >
> > thanks a lot
> > -as
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 18 2001 - 08:56:28 CST