difference between schema and user

[Sweetie]

Hi

I am quite confused of the concept of schema and user, it seems to me that they are both the same... can anyone explain a bit?

[rpardip]

Both are one and the same.

Schema is a logical term that is defined as the
collection of all the objects (tables, indexes, triggers procedures, sequences, etc) owned by a particular user.

[Sweetie]

Hi

I dunno if this is correct what I understand these two terms are as follows

schema are objects owned by an user
however an user can access several schemas if enoguh privilege are granted to them

but people tells me that all objects that an user can access forms a schema

so what it confuses me is that

a schema forms the group of objects that an user can access including objects not created by the user

or

a schema are only those objects that are created by an user?

[sreddy]

sweetie,

By any chance you have Enterprise Manager. If you open DBA studio and select schema view 'by object' and 'by schema' you can see the difference with your eyes. Even if you don't have OEM set up for your enterprise, you can set up OEM client on your terminal and login locallly without Management server.

AFIK, Schema can be mentioned in terms of 'Ownership'. Unless otherwise the user creates objects, user can't be owner of those objects. User might be having equivalent permissions of Owner(select,insert,update,delete) on other's objects, he can't be owner of objects.

So, to my understanding:

schemas are collection of objects that are created by an user?

NOT

schema forms the group of objects that an user can access including objects not created by the user.

[chrisrlong]

True, as far as Oracle is concerned.

The confusion probably stems from the fact that Oracle did not invent the term 'Schema', AFAIK. And if they did, it has since been twisted.

From the application side of the house, the database schema is the same as the model is the same as the set of objects that the application uses, whether that is through views, tables or synonyms, regardless of who owns them. Furthermore, when I log into the application as a user, the schema is 'what I can see' as a user, which can be different from the application's definition of the 'schema' and different still from the database's definition of the schema.

From Oracle's perspective, the term 'schema' is as sreddy defined it, AKAIK, the set of objects actually created by ( or owned by) a given user.

HTH,

- Chris

[akkerend]

Strictly a schema is the set of objects owned by a user, as rpardip defined.
With the right privileges a user can create objects in another users schema.

[sreddy]

Well! Thats what Chris talking about. Its not specific definition. From application perspective its different, Database perspective diffrent. We use often in conversation in different contexts diffrently.

From DBA perspective, If I can't see other user objects under that schema, I don't call it is part of the schema where as application can see it and from application perspective, Hence its called schema(your definition) as it includes other's objects too.





[Edited by sreddy on 01-25-2001 at 09:25 PM]