DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: VPD vs Multiple Schemas

  1. #1
    Join Date
    Jul 2000
    Pune, India

    VPD vs Multiple Schemas

    We are into healthcare software and now customers are requesting new feature from our application, they want to run application in their group of hospitals on single Oracle database. Available options are listed below. what do you suggest? if I am missing any point please let me know

    Single Schema (using Virtual Private Database)
    Maintaining all database objects in the main schema and create user for each hospital to access data from main schema. Hospital id will be defined for each hospital and will part of PK of all tables. Security policy based on hospital id can be defined for each table, which allows other users to do DML (data-manipulation) as well as SELECT operations only on those records in a table, for which a user has access.

    Application Level
    Advantages: No changes are required in our application.

    Database Level
    Advantages: Easy to manage and maintain
    Easy to upgrade and patch (Oracle)
    SGA can be defined adequately
    Advantage of sharing Sqls

    Disadvantages: VPD concept may sound complicated (to Management)
    Downtime of All hospitals will be the same incase our application version needs to be upgraded
    Performance overhead (10%?) for SELECT while doing join operation or sub select
    Backup time will be more

    Multiple Schema's:
    Create Schema for each hospital and maintain all schemas in single database. Each User will access own schema objects.

    Application Level
    Disadvantages: All References to main schema needs to be removed from our application. (e.g. Schemaname.TableName)

    Database Level
    Advantages: Looks Simple and easy to understand (to Management)
    Downtime can be planed for each Hospital for upgrading our application version
    All hospitals can run on different versions of our application
    Schema level backup can be scheduled

    Disadvantages: Difficult to manage and maintain xx schema's
    Difficult to upgrade and patch (Oracle)
    SGA (Oracle Memory) to increase xx times (shared sql will not work)
    Consumes more disk space (data, archive logs)
    Accessing the Oracle data dictionary will have performance overhead
    latching on the shared pool will be more

    Please let me know.

    Thanks & Regards.

  2. #2
    If the hospitals might want to be on different versions then VPD is out. If different versions might require different Oracle versions then you need at least a small set of Oracle instances to cover the version requirements.

    I strongly advise against putting multiple hospitals in the same schema w/o VPD, the security problems could be serious.

Posting Permissions

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

Click Here to Expand Forum to Full Width