Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rule of thumb for new schema vs. new database?

RE: Rule of thumb for new schema vs. new database?

From: <>
Date: Thu, 6 Oct 2005 13:34:09 -0000
Message-ID: <>

Hi Brian.

To put up a separate instance is pretty extreme to me, Oracle is a database that can handle many kinds of applications. I would recommend another schema as that takes up far less reasources on your machine.

Regarding data recovery, if you need to recover a schema or tablespaces, you can to a Tablespace Point In Time Recovery onto a separate instance using RMAN and recover from there. In 10g you can use flashback recovery on tables to roll back. In my experience this does not happen that often that it justifies a whole instance.

Jon A. Einarsson

-----Original Message-----

From: [] On Behalf Of Mercadante, Thomas F (LABOR) Sent: 6. október 2005 11:55
To:; Oracle-L
Subject: RE: Rule of thumb for new schema vs. new database?


Something that was not mentioned by everyone and what I think is the most important question to ask has to do with recoverability.

If you need to roll one schema back for a data recovery reason, then the planning takes on another whole level of thought. In my mind, doing individual tablespace recoveries for this reason (if you separating each schema into separate tablespaces) is just not worth it. I prefer creating a new database instance on the same machine. Also, if these are canned applications separate instances gives you more flexibility if these applications are tied to specific release levels of Oracle. Right now I am unable to move to Oracle 10g for some of our databases because our current level of Curam (our current software du jour) is not certified against it. But we have other applications that can move to 10g.

To me, it is not a big deal to create another instance. Sure it consumes more hardware resources, but it makes recovery a lot easier.


-----Original Message-----

[] On Behalf Of BP Sent: Wednesday, October 05, 2005 5:09 PM To: Oracle-L
Subject: Rule of thumb for new schema vs. new database?

Hi Again,

I have a meeting tomorrow with one of our project managers who is going to request a new schema or database. He wants my advice on which is best for his needs. I've said before I'm a neophyte DBA and do know the difference between a schema and a new database. I'm inclined to suggest just a new schema, but now I'm wondering to myself what kind of 'rule of thumb' is there is for this kind of decision.

When would it be preferable to create a new db vs. just a schema in an existing db?

Thanks for you help.

Brian Peasey
-- Received on Thu Oct 06 2005 - 08:42:54 CDT

Original text of this message