Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Befits of separate schemas.

    Are there any performance benefits to be gained from splitting a project across more than one schema? Or it is mainly for administrative or security purposes?
    Coming from a SQL Server environment, the closest thing we have to Oracle Schemas is user ownership, and even that is rarely used. Mostly, all commonly accessed objects are DBO owned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    schemas are logical, not physical - so I can't think of any performance reasons to split (unless maybe if you've got tens of thousands of objects in a single schema)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's what I thought.

    Thanks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by blindman
    ... Or it is mainly for administrative or security purposes? ...
    Correct, splitting schemas may somewhat benefit db administration and security.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    We treat schemas in Oracle like Sybase/SQL Server databases, separating out application areas into their own schema/database. There is nothing separating you from collecting everything under one schema, which I think could be managed with a good naming standard:

    app1_table1
    app1_table2
    app2_table1
    ...etc

    But I find it easier to separate them, myself.

    -cf

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    For me, it depends on the type of application on whether or not it will have different users. For example, for web applications, I always tend to create an additional user (for security purporses) with read only priviledges to which I attach the web services, since I don't like the idea to run web services (which interact with the db) with a priviledge user.

  7. #7
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Quote Originally Posted by JMartinez
    tend to create an additional user (for security purporses)
    Absolutely - create users for each purpose with just the privs they need

    I treat the dataowner schema as a priviledged, DBA managed account - don't let the application or any other user use that logon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •