Re: Question about schemas and tablespaces and users

"Thelonious Georgia" <keepberthasurfin_at_hotmail.com> wrote in message news:9crsd9$g9s$1_at_news.panix.com...
> Aloha all-
>
> Okay, this has been bugging me for awhile, and I'd like to get it straight
> in my head once and for all.
>
> I understand why you would have multiple tablespaces, and as they directly
> relate to files on the local machine. As the example goes, you can
> physically seperate your "accounting data" and your "manufacturing data"
> into different tablespaces, which could reside on different disks, etc.
 All
> good things.
>
> My confusion stems from the use of schemas and users. Having now already
> broken down the manufacturing and accounting data into different
 tablespaces
> (which in SQL Server parlance would be described as "databases"), why have
> schemas? And why should *every* user have a schema?
>
> Don't get me wrong, I like having the granularity insofar as that I can
 work
> in my schema on a section of the database, while other developers can work
> on their pieces, but as far as I can tell, that's where the necessity
 ends.
> In a production environment, where we have, say, a hundred users logging
 in
> using either a web front end or client application (say a fancy cash
> register), why should they all have schemas too?
>
> The solution we've been using is to create a PROD schema and put all the
> related table in that (and have a PROD schema for each tablespace), but
> having all these users with their own schemas seems silly and unnecessary.
>
> So my question is simply, how should I be thinking of users and schemas?
 Is
> there a better (or more correct) way to group tables for a production
> environment? Must all users *have* a schema (can some just be simple
> consumers of data?)?
>
> Thanks for setting me straight on these points,
>
> Theo
>
>
>

The idea behind this is the 3-layer model where you have logical architecture (which is what individual end-users see, which might be different for each user)
conceptual architecture (the complete datamodel, separated in *logical* parts)
physical architecture (your tablespaces) If you use schemas, you can have multiple *completely* separated applications in one database, but you can also decide to grant access to *part* of that schema to other users.
I hope to have clarified it for you, if not please respond.

Question to you: how did you manage to create multiple PROD schemas? A schemaname is IIRC, unique!

Hth,

Sybrand Bakker, Oracle DBA Received on Thu May 03 2001 - 16:28:19 CDT