Re: same application on multiple schemas

Palooka wrote:
joel garry wrote:
On Apr 14, 12:09 pm, Alberto <alberto.rol...@xxxxxxxxx> 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


The difference, however is available memory for SGA space. If I need 10 databases with an SGA of 5G (yeah rather large for most stuff), then I need a minimum of 50GB+free memory for things like - say - the OS and database connections. If I have separate schema, then I only need one very large SGA/process counts etc... to handle those same 10 databases/1000 users. VPD/FGAC is a way around it, but, like anything in Oracle, YMMV and test, test test and test some more...

.

Relevant Pages

  • Re: same application on multiple schemas
    ... 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 ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • Re: same application on multiple schemas
    ... 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 ... For every customer that will use our application, ... will use schema "SchemaB". ...
    (comp.databases.oracle.server)
  • Re: same application on multiple schemas
    ... 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 ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • Re: Graphical Oracle administration
    ... A 'database' in SQL Server is used to colocate data and tables which are ... this is the purpose of the SCHEMA in Oracle. ...
    (comp.databases.oracle.server)
  • Re: oracle grants/roles v sql server
    ... How is that done in Oracle? ... allow him/her to select from any table in any schema in the database. ... You could write a script to automate this though, ... means something very different in Oracle than it does in SQL Server. ...
    (comp.databases.oracle.server)