Re: same application on multiple schemas

From: Palooka <nobody_at_nowhere.com>
Date: Wed, 15 Apr 2009 01:45:23 +0100
Message-ID: <D6aFl.13472$E45.6968_at_newsfe26.ams2>

joel garry wrote:
> On Apr 14, 12:09 pm, Alberto <alberto.rol..._at_gmail.com> wrote:

>> i'm the original poster. i think that i explained the situation with
>> too much hurry. here is a better explanation
>>
>> As for the "comparison" with Sql Server, it was not intended to ask
>> why Oracle does "not" support sql server behaviour :) I know oracle (I
>> worked with it before using Sql Server) and I know that the concepts
>> behind are slightly different from those in Sql Server.
>> From you, I would just like to know if having a different schema for
>> each application is ok or not (see below the description of the
>> situation).
>>
>> we have a web application. it uses a set of tables to store its data.
>>
>> For every customer that will use our application, we will clone it.
>> Customer A will have url customerA.ourcompany.com. The application
>> will use schema "SchemaA"
>> Customer B will have url customerB.ourcompany.com. The application
>> will use schema "SchemaB".
>>
>> Every schema will be blind to any other (that is, it will not need and
>> will not be able to access any other database).
>> In the beginning, all customers will be on a small number of server
>> (maybe one, for example). If any of the customers will grow very much
>> (the application is one that can have very different sizes among
>> customers), it could happen that we will need to move it to another
>> server. This why I asked if Oracle supports detach and attach. But to
>> do this, every schema must use its separate datafiles and tablespaces.
>> Or we can use exp and imp.

>
> This is very common with commercial packages. Up until recently,
> however, there were bugs that would do things like update the wrong
> schema only under certain heavy load conditions (! non-public bug
> 5458753, referred to elsewhere like bug 6351293) or give performance
> problems.
>

>> Anyway, after my post, I studied a bit Oracle RAC, and we feel that
>> this is a technology that could fit very well our needs.

>
> Only true IF you spend the time and money to have a staff that
> understands what is going on AND write the app properly. An
> improperly written app will scale the problems faster than the
> performance, and insufficient staff will compound that. Google around
> for the classic paper called "you probably don't need RAC." That
> paper's perhaps less true now that there are more people with
> appropriate experience floating about, but still, too many places
> create some inappropriate expectations.
>

>> Just as an additional question, if using a schema for each application
>> is NOT the correct solution... which is the correct one ? We do not
>> want to use a single schema putting all customer's data in the same
>> physical table, because every customer can have its own data and we
>> would have to put everytime a "where customer = current customer".

>
> VPD (Virtual Private Database, also called FGAC - Fine Grained Access
> Control) is designed to address this, I'm not convinced, since it has
> some strange limitations and requirements. Some people swear it is
> great, google around. Even so, I don't think it is appropriate for
> your requirements, but it basically puts everytime a "where customer =
> current customer" automatically behind the scenes.
>
> Whatever you do, use bind variables and understand the "bind variable
> peeking" problem (search asktom.oracle.com if that makes no sense).
>

Forgive me if I am speaking out of turn.

Quite seriously, as far as Oracle is concerned, fsck FGAC, or even separate schemas. If I am dealing with multiple customers, each will have his own database. They might well be on the same server, though.

Palooka Received on Tue Apr 14 2009 - 19:45:23 CDT