Re: Multiple Databases under Oracle

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 7 Nov 2000 09:23:46 -0000
Message-ID: <8u8hn6$75p$1@soap.pipex.net>

"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

Received on Tue Nov 07 2000 - 03:23:46 CST