Re: Schema Inter-dependencies

From: Mark D Powell <>
Date: Sat, 15 Nov 2008 12:33:26 -0800 (PST)
Message-ID: <>

On Nov 14, 9:52 am, D-Squared <> wrote:
> I am at a new position where they host multiple applications in Oracle
> (9.2.07, just migrated to 10gR2) that are all hosted on the same
> database.  The applications have references across schemas, nothing
> special there.  My question, coming from a SQL Server environment, is
> what is best practices for this type of environment?  I understand if
> multiple schemas have interdependencies if they are all a part of the
> same external schema.  However, I question what the best practice is
> if I have multiple apps, (A, B, C....Z) and B has external
> dependencies on A's schema objects.  Is this considered best
> practice?  Or would it be better to have each app (and associated
> schemas) reference each other through views?  For example, for the B
> app and B schema to get to the A apps A schema, would it be better to
> create view B.V_EXTERNAL_1 which selects out of A.V_EXTERNAL_1?  This
> way if the schema in A or B changed I would not have to rewrite
> application code or SP's.
> Also, what do I do if A is a user tracking application that holds a
> distinct ID of all users in the enterprise.  B needs to validate the
> email and contact info of a user in the enterprise. Should B, a
> completely seperate app living on the same database, directly
> reference A?
> Any help you could provide would be greatly appreciated!

The only real issue with multiple owners is the need to grant References from one owner to another if the tables are related and FK constraints are defined to manage the data relationship. Grants to the users are handled via roles. Where the tables belong to a specific application and there is very little data overlap to other applicaiton tables stored in the database having a separate owner can assist management of the application.

For most, but not all, of our home grown applications we use one owner while we retain a separte owner for each of our vendor supplied products.

Also Sybrand had a point. Oracle and SQL Server are not designed the same way and do not work the same way. Code developed on one may not work as expected on the other usually due to the differences in the read consistency models. Make sure, if you are going to be working on both, that you learn and develop an understanding of those differences.

HTH -- Mark D Powell -- Received on Sat Nov 15 2008 - 14:33:26 CST

Original text of this message