Re: Meaning of an INSTANCE.

In article <01bbc8d9$0bb6ac00$4b7eaac2_at_msdusr>, Nabil Courdy <moab_at_emirates.net.ae> writes
>
>I am having trouble with the meaning of an instance. If
>I have a very busy production machine, does it make sense
>to have more than one instance? What I am trying to say is,
>for each database, say: payroll, manufacturing, and hr,
>do I have to have three instances, or somehow I create
>all three databases into one instance? Because, if I have
>to have three instances, that seems like a lot of processes
>running to service them.
>
>Thx.
There are three key objects here. INSTANCE, DATABASE and SCHEMA

INSTANCE is a collection of processes managing a single DATABASE.

DATABASE is collection of files managed by a single INSTANCE. (The exception is Oracle Parallel Server. Ignoring this for now we can say there is a one to one relationship between database and instance.)

SCHEMA is a collection of tables (and other database objects) owned by the same user, but accessible to any user granted the appropriate permissions.

For your three applications, you have two options. You can either create a separate database and instance for each one, or you can create three schemas in a single database. The decision on which complex. Factors to consider are.

Multiple Instances.
Pros.
  A degree of independence for applications. You can shutdown the HR instance for maintenance without affecting the other instances.   Easier to move an application to another machine if it is already a separate instance
Cons
  Each instance has an overhead, the most significant of which is the SGA, which can occupy lsrge amounts of memory. The number of processes is not significant in the context of all the user processes which will be accessing it.

Multiple Schemas
Pros.

   Less overhead.
   Easier and more efficient data sharing. It is likely that Payroll and HR at least will have some data in common. It is possible to share data across instances, but it is much more efficient and easier to manage if they are in the same database.
Cons

   All applications are interdependent. If the system has to come down for one thing, all users are affected.

This is a very simple list. Think about it carefully.

-- 
Jim Smith