Forum Stats

  • 3,770,415 Users
  • 2,253,104 Discussions
  • 7,875,447 Comments

Discussions

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

1018252
1018252 Member Posts: 2
edited Jun 20, 2013 5:06PM in SQL & PL/SQL

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.

Tagged:
1018252

Answers

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown

    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
    1018252 Member Posts: 2

    I have made correction to original post.

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown

    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

    1018252
  • 9b631776-4fa1-4f58-89e1-7d700432a1fc wrote:
    
    Hi,
    
    Please can you confirm which statements are true and which are false.
    
    
    A USER can have only one SCHEMA.
    A USER can have zero to many SCHEMAs.
    A USER can have one to many SCHEMAs.
    
    
    
    A SCHEMA can be owned by zero to many USERs.
    A SCHEMA can be owned by one to many USERs.
    A SCHEMA must be owned by one and only one USERs.
    
    
    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.

This discussion has been closed.