Re: same application on multiple schemas
Date: Tue, 14 Apr 2009 13:35:03 -0700 (PDT)
Message-ID: <d6d516f5-db38-4f54-9761-efee217cf51f_at_x29g2000prf.googlegroups.com>
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).
jg
-- _at_home.com is bogus. http://www.ericsiegmund.com/images/fireant/photography/portajohns.jpgReceived on Tue Apr 14 2009 - 15:35:03 CDT
