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

Hi Trevis,

Almost bedtime some I'll be quick.

A database in SQL server speak equates to a schema in Oracle speak.

To have different sets of tables you wish to independently access, you can simply create multiple schemas in the same database. Note that an Oracle database relates to all the physical files that constitutes the database and that an Oracle instance relates to the SGA memory structure and background processes used to "access" and support the Oracle database.

Note that a user and a schema are interchangeable, they basically relate to the same thing. A schema is just a collection of objects that belong or are owned by a user. All objects must have an owner and only the one owner.

Therefore you can simply CREATE USER bowie IDENTIFIED BY bowie_password ... (see SQL Reference manual for full syntax) to create your first user/schema (or database in SQLserver). Then connect as bowie/bowie_password and create all the tables and indexes and whatever in this schema.

Then you CREATE USER ziggy IDENTIFIED BY ziggy_password blah blah to create your second user/schema. Then create all the objects you wish in this schema. Note you can have (say) tables in one schema with the same name as objects in the other schema as they are actually separate tables.

Then you can connect as whichever user to access the appropriate schema objects. Note you can grant privileges as one user/schema to allow the other user/schema access to your objects (and visa versa).

Hopefully this helps rather than confuses your understanding. I would strongly recommend reading the Oracle Concepts manual and Tom Kytes Oracle Expert One-on-One book to help you with all this.

Good Night :)

Richard

"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. 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. 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.
> 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.
>
> Thank you!
>
> T
Received on Mon Sep 30 2002 - 09:24:18 CDT