Forum Stats

  • 3,770,016 Users
  • 2,253,049 Discussions
  • 7,875,269 Comments

Discussions

multiple schema under a user....

661723
661723 Member Posts: 839
edited Aug 22, 2012 6:02AM in SQL & PL/SQL
sir

may be its look a bit Odd that i am going to ask here.

1)do oracle 11g/9i supports multiple schema?

2) if not why?I mean:
As it is seen that other DB eg. DB2 supports multiple schema but why not oracle?wat is the philosophy behind this?Is it to avoid the security reasons which may come up for using multiple schema under the same user?

thanks in advance

Best Answer

«1

Answers

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    A schema in Oracle is the set of objects owned by a user. You can have a user without a schema (particularly with enterprise user accounts). But there is exactly 1 user account associated with each schema.

    I'm not familiar enough with DB2 to be able to answer with much specificity. But I would tend to suspect that there is a difference in terminology. Just like what other database products like SQL Server call a "database" is very different from what Oracle calls a "database" and is more analgous to what Oracle calls a "schema," I tend to suspect that Oracle and DB2 use "schema" to mean two different things.

    Justin
    JustinCave
  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    Hi,

    Perhaps I don't understand your question but, by definition, a schema is the whole set of objects owned by a user.

    Are you referring to the access rights a user can be granted on other schemas?
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    Yes you can have multiple schemas in Oracle (in every version for the last 20 years at least). The "under a user" part is puzzling though. Can you describe the scenario you're thinking of?
  • Satishbabu Gunukula
    Satishbabu Gunukula Member Posts: 659 Gold Badge
    edited Sep 16, 2009 2:30PM
    See the below link to understand the Schemas in oracle.

    http://download.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_6013.htm

    Also see the doc to manage schemas through EM.
    http://www.oracle.com/technology/obe/2day_dba/schema/schema.htm

    Hope this helps.

    Regards
    Satishbabu Gunukula
    See the link to [ Identify weather using PFILE or SPFILE in Database|http://oracleracexpert.blogspot.com/2009/09/how-to-change-static-parameters-in.html]
    http://oracleracexpert.blogspot.com

    Edited by: Satishbabu Gunukula on Sep 16, 2009 11:30 AM
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    Accepted Answer
    Well, yes there is a <tt>CREATE SCHEMA</tt> command but it's a bit of a curiosity. I don't think anyone has ever actually used it.
  • 661723
    661723 Member Posts: 839
    say an user is hr.and he creates a schema say product is he can that?
    as you said he can.

    now he could handle perform the DCL statement also on this i hope so.
    As it has less usability that is why my faculty said in oracle user word is a synonym of schema.

    (if i have correct understanding over the link posted here:)
    but that schema must contain a table and a view.

    regards
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    mango_boy wrote:
    say an user is hr.and he creates a schema say product is he can that?
    as you said he can.
    No. The CREATE SCHEMA command actually has nothing to do with creating a schema. It is a rarely useful way of bundling multiple DDL statements together as a single transaction. As Andrew points out, CREATE SCHEMA just allows you to create multiple objects in an existing schema.

    The HR user, if you were sufficiently cavalier about the security implications, could create a user named PRODUCT (with the CREATE USER privilege). The HR user could create a number of tables owned by PRODUCT (with the CREATE ANY TABLE privilege). That would allow the HR user to create and populate a PRODUCT schema. But since privileges like CREATE ANY TABLE are incredibly powerful, it would almost always be a bad thing to do.
    now he could handle perform the DCL statement also on this i hope so.
    I'm not sure I understand this comment.
    As it has less usability that is why my faculty said in oracle user word is a synonym of schema.
    I'm not sure what "it" refers to and what "it" has "less usability" than.

    In an introductory class, it is probably reasonable to state that a user and a schema are synonymous. That is not strictly true, but going in to enough detail to explain all the differences is likely to confuse most introductory students. Just as your physics teachers teach Newtonian mechanics long before they start mentioning that Newtonian mechanics is a simplification of reality that doesn't work as speeds near c, Oracle instructors will commonly simplify things and state that users and schemas are synonymous when that is not strictly true because it happens to be true for the vast majority of situations you are likely to encounter.
    (if i have correct understanding over the link posted here:)
    but that schema must contain a table and a view.
    I don't follow this...

    Justin
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    edited Sep 17, 2009 6:30PM
    say an user is hr.and he creates a schema say product is he can that?
    HR can create a set of database objects (given sufficient privileges). Why would he want to give a name to the set of objects he owns?

    If HR creates a table called EMPLOYEES, that table can be referred to using dot notation as HR.EMPLOYEES, because it's called EMPLOYEES and owned by HR. I can't see a need for another layer of naming.

    If you wanted PRODUCT.EMPLOYEES then PRODUCT should have created the table.
  • Hans Forbrich
    Hans Forbrich Member Posts: 11,543
    mango_boy wrote:
    now he could handle perform the DCL statement also on this i hope so.
    As it has less usability that is why my faculty said in oracle user word is a synonym of schema.
    From the 11gR2 Concepts manual http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/intro.htm#i68236

    "One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects. A database schema is owned by a database user and has the same name as the user name."

    If that user has the correct privileges, it may run DDL (create, alter, drop) for it's own schema. With different privs it can perform DDL against any other schema.

    I'd encourage you to read the documentation. Specifically Chapter 1 of the Concepts manual.
  • 723361
    723361 Member Posts: 1
    On a different, but related note, I would like to run my app as a lower-privileged user, (who can't do things like create/drop/alter,) while having another user do my DDL at deployment time. Is there an idiom or best-practice for this? I would like to avoid having to qualify all of the object names with the schema name in my DML at runtime. I can live with qualifying them in the DDL at deployment time, but I would be interested to know if it is possible to work around this?

    TIA

    ADK
This discussion has been closed.