Re: Multiple Databases under Oracle

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