Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multiple SCHEMA under a single USER, Multiple USERs associated to a single SCHEMA

1018252Jun 20 2013 — edited Jun 20 2013

Hi,

Please can you confirm which statements are true and which are false.

  1. A USER can have only one SCHEMA.
  2. A USER can have zero to many SCHEMAs.
  3. A USER can have one to many SCHEMAs.

  1. A SCHEMA can be owned by zero to many USERs.
  2. A SCHEMA can be owned by one to many USERs.
  3. A SCHEMA must be owned by one and only one USERs.

USER in this case means Database Object.

Comments

JustinCave

Define "user account".  That is not a term that I've ever seen specifically defined in any Oracle documentation.

Define "user".  Are we talking about an Oracle database user?  An application user?  Are we restricting ourselves to "normal" database user accounts or can we consider things like externally authenticated accounts and enterprise user accounts.

Define what it means for a schema to be associated with a user account.  "Associated" in this context is very much open to interpretation.

Justin

1018252

I have made correction to original post.

JustinCave

A schema is the set of objects owned by a user. So "A SCHEMA must be owned by one and only one USERs." is likely to be the correct answer (though "own" isn't really the right word).

Whether every user has a schema is a matter of opinion.  Most GUI tools, such as Enterprise Manager, if they list schemas, will only list those schemas that contain at least one object.  On the other hand, from a set theory standpoint, there is no real reason to object to an empty schema.  A "regular" Oracle database user (excluding enterprise users) is the owner of either 0 or 1 schemas.  So "A USER can have only one SCHEMA." is likely to be the correct answer (though "have" is a very imprecise word).

Justin

1 person found this helpful
unknown-7404 
9b631776-4fa1-4f58-89e1-7d700432a1fc wrote:




Hi,




Please can you confirm which statements are true and which are false.




    

  1. A USER can have only one SCHEMA.
    2. 

  2. A USER can have zero to many SCHEMAs.
    3. 

  3. A USER can have one to many SCHEMAs.
    4. 





    

  1. A SCHEMA can be owned by zero to many USERs.
    2. 

  2. A SCHEMA can be owned by one to many USERs.
    3. 

  3. A SCHEMA must be owned by one and only one USERs.
    4. 





USER in this case means Database Object.

The second set of questions don't make ANY sense if a USER is a Database Object.

See the Database Concepts doc for the answer to the first set of questions

http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm

Introduction to Schema Objects

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 18 2013
Added on Jun 20 2013
#oracle, #schema, #sql, #user
4 comments
3,758 views