Re: Multiple "databases" in Oracle...from a SQL Server Admin view..HELP

"Trevis" <trevisc_at_yahoo.com> wrote in message news:30e0b07d.0209300541.3a5cd298_at_posting.google.com...
> Hello all you Oracle Gurus!
>
> I'm really confused about setting up more than one database in oracle.
> I've read multiple posts about "Instances" and "Schemas" and i'm more
> confused now then ever.

OK, some definitions to help you on your way. An instance is the set of memory structures and processes through which access to the database is gained, and where all the processing of a select (or other SQL) statement takes place.

A database is the set of physical files on disk where data and transactional information is permanently stored. There is a minimum of 4 files which make up an Oracle database: a control file, a datafile and two online redo logs.

A schema is just a collection of objects (tables, indexes, clusters and so on) owned by a user. It doesn't physically exist, but is a logical grouping of objects by owner. Oracle does have a physical grouping structure, too, called the tablespace. That collects a group of objects by their physical placement on disk.

The SQL Server equivalents are notoriously hard to pin down, because (after all) they are two completely different systems. But a SQL Server database is more like a schema or tablespace than anything else: you use SQL Server databases to break a complete SQL Server server down into smaller pieces. Precisely what a tablespace does (physically) or a schema does (logically).

An 'instance' of SQL Server is (as I understand it) the entire system: you install the software onto your Windows box, and you get a whole set of memory and process structures, together with some starter 'databases' (like "master"). That's what we'd call an Oracle Server (the instance+a database). What Oracle calls a database, SQL Server doesn't have a name for, I think: it's actually the collection of all your 'databases' put together. And SQL Server doesn't have a name (as far as I know) for what we call an Instance -there's no identifiable name in SQL Server for the bunch of processes and memory structures needed to make SQL Server work.

>Here is what I want to accomplish. I want to
> set up a box that I can use for testing VB applications against.
> There are multiple applications that use different databases(tables).
> In SQL server I just create a new "database" and create an odbc
> connection that uses that database's tables as its default.

Well, with the above definitions in mind, the equivalent in Oracle is to have one database, but multiple schemas (and potentially, multiple tablespaces if you want the two sets of data to be physically separated from each other, as well as logically).

>How do I
> accomplish this in Oracle 8.1.7? I've tried creating 2 databases but
> it seems like the first "Instance" that loads hogs up all the
> resources and even though I see the second database I can't access it.

As I say, you only need one database, but two schemas or tablespaces. The subtler problem here is how to get two different Instances running on one box (which I don't think actually is what you are after) -you'll have to delve into the depths of the Oracle init.ora file, and start modifying some of the memory parameters there so that your Instance doesn't behave as such a resource hog.

The reason I don't think you want to go down this route is that if you have two separate Oracle databases, you'll always have to establish two completely separate ODBC links to the databases to use both sets of data. If you use a single database, you need only create two users (which is how you create schemas):

create user app1 identified by app1 default tablespace APP1 temporary tablespace temp quota unlimited on APP1;

and

create user app2 identified by app2 default tablespace APP2 temporary tablespace temp quota unlimited on APP2;

(Of course, those two tablespaces need to exist beforehand, so a "create tablespace APP1 datafile 'C:\wherever\app1.dbf' size 100m;" and a "create tablespace APP2 datafile 'C:\wherever\app2.dbf' size 100m;" would be needed first).

Now you make a single ODBC connection to the database, and -depending on who you connect as- the relevant set of data will be visible. Were you to do something like this:

create user superone identified by password; connect app1/app1
grant select on APP1TABLE1 to superone;
grant select on APP1TABLE2 to superone;
connect app2/app2
grant select on APP2TABLE1 to superone;
grant select on APP2TABLE2 to superone;

...and so on, then the single connection could be made as user SUPERONE, and both sets of application tables would be simultaneously visible. You couldn't do that with separate databases.

> I'm sure i'm missing something really simple because I will be the
> only one accessing the data and I don't care about performance I just
> care about having different databases on one machine. Please post any
> friendly advice. My company won't pay for training but I could
> purchase books if there are any that would help.

It's a shame they won't pay for any training, because I'd say that 5 days spent on the DBA1 Fundamentals course would pay enormous dividends. Converting from one database terminology to another is notoriously difficult. You get loonies saying "well, they're all relational databases, aren't they?, so they all work fundamentally the same way" -which has a grain of truth in it, but hides real and fundamental differences. When the word "database" means two completely different things in two products, its a fair bet that making the transition is not a walk in the park.

If you really can't persuade them to spring for a single 5-day course, shame on them, and hard luck to you. Try reading a couple of excellent books, such as (ahem) 'Beginning Oracle Programming' or 'Expert One-on-One' (both published by Wrox).

Otherwise, try not to translate SQL Server into a one-to-one correspondence to Oracle. I've been there (the other way round) and it fundamentally doesn't work. You really need to try to approach each product independently, treat it on its own terms, and get inside the mindset of each one as though it were the only product out there.

Best of luck
Regards
HJR
>
> Thank you!
>
> T
Received on Mon Sep 30 2002 - 15:35:58 CDT