Re: Informix to Oracle question

It seems to me that the Informix 'Database' is analogous to the Oracle 'Schema'.

The Oracle 'Schema' is the set of object (read table, index, sequence, procedure, etc.) definitions AND their physical implementations managed by one 'OWNER'. (An owner just happens to be a USER who has DDL write permission). Frequently a Schema is a collection of related objects to perform one business function or store data for one application.

An Instance is the collection of processes that interact with one database BUT a database can hold any number of Schemas.

In practise (and frequently because DBAs don't know better - although this is subject to a lot of decision making) one database will only have one (application) schema. Thus DBAs will end up with multiple instances (app1-test, app1-prod, app2-test, app2-prod) which uses a LOT of memory and CPU, when they might have been able to get away with fewer (test, prod - each containing app1 and app2). I view the Oracle database as the unit of maintenance - upgradability, backup, restore, etc. - and that view helps decide how many instances to have.

The table level recovery is a different animal altogether - if you have Oracle 8.1.5 or higher (please say 8.1.6) then look at LogMiner to help with that.

Basic concept in Oracle - use the following philosophical mapping:

Concept            Computer  (UNIX)          Oracle
Thing running     Operating System             Instance
Major storage    Disk subsystem (RAID)   Tablespace
Stored think       File                                  Table/Index
File owner         User                                 Schema

A tablespace can store many different tables/indexes/etc. even if they are owned by different Schemas. Just like you specify specific files to be placed on specific disks, you can specify tables be placed in specific tablespaces. Decision processes is based on maintenance requirements.

There are pros and cons to the restore method you describe for Informix, just as there are for Oracle. It's different and can be difficult to get your head around. Be patient and read the manuals. Oracle Press (Osborne?) has a few good books on the subject.

/H

Kevin Brand wrote:

> I too converted from Informix several years ago ( just as Informix V7 came
> out ).
>
> It seems that you may have the Informix terminology mixed up. You say
> "...have seperate instances of databases." Well, Informix architecuture is
> as follows:
>
> Instance = collection of background processes that represent the RDBMS
> Database = named collection of schema objects ( tables, triggers,
> procedures, etc.. )
> User = OS authenticated, used for permissions to access databases and the
> objects contained within a database.
>
> Informix allows the creation of multiple databases ( as defined above )
> within an Instance by using Create Database statement, then allows access
> via database permissions on OS level userids.
>
> Oracle terminology/architecture is a bit different:
>
> Instance= collection of background processes that represent the RDBMS
>
> Database= usually spoken in reference to the entire Instance. That is, a
> database and an Instance of the RDBMS can be "seen" as the same thing in
> Oracle.
>
> User = a userid defined within the database/instance. Each user is
> analogous to the "Database" in Informix speak. That is, each User in an
> Oracle database is the same thing as a Database in Informix.
>
> So, for example, if you have an Instance of Informix, say
> ONCONFIG=instance1, and within the instance you have three databases defined
> as db1, db2, and db3, a comparable Oracle configuration would look like
> this:
>
> SID=inst1
>
> Within the inst1 instance, you'd define three users ( or Schema's ) using
> the Create User SQL syntax ( db1, db2 and db3 ). Each of the new schemas
> would contain their own set of objects, which could ( for best results ) be
> placed in their own separate tablespaces on different disks/directories ( as
> you stated ).
>
> As for recovery, Oracle and Informix vary drastically here, so you'd be
> better off just diving into the backup/recovery documents.
>
> Hope this helps...
>
> -Kevin
>
> "VWP914" <vwp914_at_aol.com> wrote in message
> news:20001117013307.15477.00000166_at_ng-bh1.aol.com...
> > Hi everybody:
> >
> > I come from an Informix background where we log into a particular database
> > instance by going to the directory, and typing 'isql' to get in. In
 Oracle, it
> > seems that all the tables are kept in one central location and only
 seperated
> > by users. This leads me to wonder what happens when you run out of space
 on
> > that partition. Is there not a way to have seperate instances of
 databases?
> > For example, can a marketing database be kept in a different directory
 from an
> > accounting database, etc?
> >
> > My other question concerns backup. In Informix, when someone screws up a
 table
> > really bad, we just restore the files that make up that table. Does the
 same
> > hold for Oracle?
> >
> > Any help would be much appreciated.
Received on Sat Nov 18 2000 - 22:28:21 CST