Re: schemas & tablespaces

<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/
Received on Thu Jan 18 2001 - 01:37:18 CST