Re: same application on multiple schemas

From: Michael Austin <>
Date: Fri, 10 Apr 2009 08:49:19 -0500
Message-ID: <M7IDl.4500$>

Alberto wrote:
> Hello, I'm designing a new big application.
> The application will be instantiated for multiple users. Each
> application must have its own "database", or schema, Oracle speaking.

How many users per schema? This "scheme" sounds like a disaster waiting to happen. Do they need separate schemas or do they need separate databases instances?

> Every schema should have separate datafiles in its own application
> folder. Databases will be on a disk array, in common with multiple
> database servers.
> I would like to be able to separate schemas so much so that, when
> needed, a schema can be detached from a database server, and attached
> to a new one.

Oh, like SQLServer? not sure why you would never need to do that... I have not found a good reason yet...

Is this for DR or something different?

> Is this possible ? What is the best way to do that ?
> I come from the microsoft sql server world, and here each database
> (the schema in oracle) can be detached and attached with ease.
> I'm looking for the same in Oracle.

If you learn to design applications properly, you may not need to do what you think you want to do... I find those sort of apps non-scalable either horizontally (RAC for example) OR vertically (bigger box(es)). Use the database engine to it's full potential. Do not cripple it with a bad application design. (I have worked on online db's that do hundreds-of-thousands/txn/hr and data warehouses that is hundreds of TB in size - never had the need to do anything like you think you want to do. I find that these sorts of "apps" designs to be inadequately define and/or funded. If you do it right the first time, you won't need to do it again.

> any help or suggestion is really appreciated!
> Alberto

[I am currently available for consulting (1-2 weeks) should you require another set of eyes to [in]validate your proposal. - Email address is real] Received on Fri Apr 10 2009 - 08:49:19 CDT

Original text of this message