Re: Multiple Databases under Oracle

There is a common misconception, dating back to the early FoxPro/DBase days, that a 'database' is a single file or a single table. This has caused many, many potential Oracle users to be completely confused and use much more system resources than necessary.

If you have UNIX background, the explanation becomes somewhar easier. Assuming you don't ...

In Oracle terms:

1. An Instance: The set of software that is running, using memory and CPU. This controls that data in a database. In UNIX terms, this could be considered equivalent to a 'process'. (Yes, I know that Oracle does start up several processes - but each has it's unique tasks and the collection is the functional part of an instance.)

I generally design things so an Instance is a unit of maintainability - backup, recovery, security, upgradability, etc.

2) A Database: The physical side, effectively the collection of all schemas, tables, indexes, etc. that are controlled by one instance.

I like to arrange a database to coincide with the purpose of the Instance. In other words, one database contains all data and applications that can (and should) be concurrrently maintained. So I may put 3 'production systems' into one database (a lot of decision making required here), but I usually keep the 'production' separate from the 'test'.

3) A Schema: The 'definition (and implementation)' of one set of tables, indexes and other related objects. A Database may have many Schemas, and these Schemas may all be identical, except for the Schema Owner.

Older (read PC) 'database' definitions are equivalent to a single Oracle Schema. This is quite understandable since most of the original PC databases were 'single user'.

Multiuser concurrent access to a single set of data is accomplished though security. A user is granted appropriate access to the contents of a schema.

In my mindf, a Schema generally equates to a single user who needs his/her own space (seems like this fits your example) OR to a single business function (such as the HR records, the GL, etc). For ease of maintenance, I like to keep a lot of Schemas in a single database - assuming I can keep appropriate security around each Schema.

4) A Tablespace: A physical (set of) file(s). I like to look at this as equivalent to a computer's disk. You can put multiple files on a disk and you can put multiple tables (indexes, etc) in a tablespace. A tablespace can consist of several computer files, just as a computer file system can consist of several physical sisks (in a RAID environment).

The objects in a Schema that require space always have a parameter to indicate where (which tablespace) that object is to be stored. Such objects include Tables, Indexes, Clusters, etc.

It's generally a good idea to keep a tablespace restricted to a single schema, but a schema may use several tablespaces. This is because the tablespace is the smallest unit of administrative maintainance (backup, etc) within a database and the tablespace permits separation for performance purposes (eg: keeping indexes on different disks than the raw data) when that becomes an issue.

5) Object: Something of a defined type that is a) owned by a schema and b) has a known set of characteristics. Tables, Indexes, Sequences are objects, but so are Functions, Java routines, etc. Common things about objects include ownership, accessibility restrictions (security), and definition.

Hope this helps
/Hans

Geoff Russell wrote:

> Niall Litchfield wrote:
>
> > Hi
> >
> > "Jerry Gitomer" <jgitomer_at_erols.com> wrote in message
> > news:20001106.5225600_at_mis.configured.host...
> >
> > >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
> >
> > On 11/5/00, 8:08:59 PM, Geoff Russell <geoff_at_austrics.com.au> wrote
> > regarding Multiple Databases under Oracle:
> >
> > > Hi,
 

> > > When I try to create more than one database in Oracle, it takes 10
> > > minutes and uses many Mb. I suspect I
> > > need to create 1 schema or one tablespace per client. Or is there a
> > > better way to do this? Basically I want
> > > a logically independent set of tables (the same table structure
> > > replicated once for each client) which I
> > > can work with by name, but I don't want to buy lots of new hard drives
> > > and work heaps of overtime just
> > > to create this set up.
> >
> > CREATE USER client1 IDENTIFIED BY <password>
> > DEFAULT TABLESPACE <tablespace_name>
> > TEMPORARY TABLESPACE <tablespace_name>;
> >
> > If you want to keep the data physically separated create a tablespace
> > for each client, but it really isn't necessary to do so.
> >
> > >>>>>>>>>>>>>>>>>>
> > An alternative approach might be to create one set of tables , each with a
> > client or owner column which is populated by the the username for the
> > client. then each select or insert needs to include 'WHERE CLIENT = USER'
> > or equivalent code. I must admit it doesn't sound like this is appropriate
> > for your needs, but what happens when you have a thousand or ten thousand
> > clients?
> >
> > You also don't say exactly why you wish to keep the datafiles seperate. If
> > you wish to back up clients data seperately then seperating data tablespaces
> > is a must. You can probably use only the one temporary tablespace. If
> > however you want to do things like moving data to client sites etc you might
> > want to read up on transportable tablespaces available with Oracle 8i.
> >
> > Hope these ramblings are of some help and don't cloud the issues.
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
>
> Thanks. I'm beginning to think schemas are what I need -- which is pretty much
> what Jerry said inthe first posting. We don't have a thousand clients, but each
> client has a complex set of tables representing
> their routes, trips, timetables etc. So I create 1 schema per client and grant
> access to any people working with
> that clients data. I can export by schema, so it looks ok.
>
> Thanks,
>
> --
> Geoff,
>
> geoff_at_austrics.com.au | Phone: +618-8332-5069
> 6 Fifth Ave, St Morris, SA 5068 | Fax: +618-8364-1543
Received on Sat Nov 18 2000 - 18:18:13 CST