Re: Basic Oracle Architecture question

Comments embedded. You'll probably receive a number of responses from more authoritative NG posters (mostly from educators).

Paul Smith wrote:
> Hello,
>
> I am new to Oracle and I am confused over a couple of basic
> architecture issues.
>
> (1) I have a multi-user application that connects to an Oracle
> database. When I create Oracle users in DBA Studio, a schema is
> created for each of the users. So user U1 is looking at a different
> set of tables to user U2. But in my application I want each user of
> the application to be looking at the same set of tables! What is the
> usual way to handle this? Do I effectively have an "application"
> Oracle user, so each user of my application connects to the database
> as the same user (and therefore has the same permissions on the
> databas objects)?
>

You create a schema owner (just an Oracle userid that has privileges to create objects such as tables). You create all the tables under this userid. Your application either connects directly to this schema owner account or to an individual userid (depending on your applicaton design). If the latter, you have a bit of additional work to do - (1) from the schema owner account, grant access on tables; (2) from individual accounts, create synonyms that associate tablename with schema.tablename.

> (2) What exactly is a Schema? I have two Oracle books, neither of
> which explain whether it is a logical concept (which I'm sure it must
> be), physical, or otherwise.
>

In the old days, "schema" referred to the metadata definition and description of a database. In Oracle terminology, it refers to the Oracle userid that "owns" (can create) objects such as tables.

> (3) I am aware that this question has been asked several times already
> on Google, but unfortunately there are many conflicting answers (I am
> hoping to get a categorical answer from a real expert out there!) - My
> O'Reilly Oracle Essentials book states that "an instance can connect
> to one and only one database" but Kyte's book tells me that "a
> database may be mounted and opened by many instances. An instance may
> mount and open a single database at any point in time." I guess these
> statements are not necessarily contradictory. Assuming they are not
> conotradictory, could someone tell me how an instance points to a
> database, and how it can be changed to point to another database.
>

A database consists of the files in which data is stored (files are physical; tablespaces are logical). An instance includes the background processes (database writer, smon, pmon, etc) and memory structures (SGA, etc). When you start/open a database, you first start the instance (which sets aside memory for the SGA and starts the background processes) then you open the database (which opens the files).

I don't want to put words into the mouths of authors you have read. I suspect the difference is that they are talking about two different architectures ... one of single server and the other of parallel server. But since Tom Kyte (vastly more knowledgeable of Oracle architecture than I am) occasionally responds on this NG, I'll defer to him on this one.

> Many thanks,
> Paul
Received on Thu Dec 12 2002 - 18:02:50 CST