Re: What is a Catalog and Database according to Oracle?

Hi Steven,

   here is what I think :

1. An "Oracle database" is an instance of an Oracle DBMS, thus if there is more than one instance running on a computer then the computer has more than one database running?

Oracle defines database as a set of datafiles that contain valid data. If you have two sets of datafiles (say named sysA.dbf and sysB.dbf), then you have two databases. An instance is a set of Oracle's background processes and memory area. An instance at any given time can 'mount' and 'open' a database ie, the instance allows access to the data in the datafiles. In day-2-day terminology, the terms instance and database are often used interchangeably.

In case you have more than one instance and each instance accesses a different set of data files, then you have more than one database on your machine.

In case however, more than one instance accesses the SAME SET OF DATAFILES, then you have a multi-instance database, which can only happen if you have Oracle Parallel Server option installed and running.

2) Do Oracle DBAs use the terms "Oracle database" and "Oracle server" interchangably? Or does "server" mean one computer with possibly multiple databases running on it.

Yes, the terms database, instance and server are usually jumbled up, but a 'server' normally means a box where there are some oracle databases and instances present.

3) Is an "Oracle user" the same as the ANSI SQL term "catalog"? If so, then a Oracle user can have only one catalog? Do Oracle DBA's call a catalog the "user's database"?

There is no term called 'User Catalog' in Oracle. An oracle user is just a user having a default tablespace and a temporary tablespace (to which he has to be given rights). A user may create objects in several tablespaces. There is no term called "users's database" either (unlike Sybase and MS-SQL Server). The only 'catalog' used in oracle is the system catalog managed by oracle itself. The term "user's catalog" is not used.

4) The Oracle docs say that a user account can only own a single schema which has the same name as the user name. Therefore, a user's catalog or user's database (identified by the user's name) contains one schema owned by the user (also identified by the user's name) and portions of other users' catalogs/schemas which the user has access to?

A schema in oracle terms is a set of objects to which the user has access. Notice that I don't mean that the user is the owner of these objects and that the owner has read/write permissions on those objects. Whenever a user is created, a schema is created for him and all his objects will belong to that schema. (I am not aware how to classify objects owned by other user's to whom a user has access.)

5) An Oracle DBA can setup up additional user accounts, say 5, for the same user ie, JSmith1, JSmith2... JSmith5. with different schema access privileges; thus, simulating one ANSI SQL "cluster", defined as a "group of catalogs" and not to be confused with Oracle cluster meaning?

This method of 5 accounts has no relevance in oracle. Each of the accounts will exist as a separate user and have a separate schema. I am not sure what is meant by a "cluster". In oracle, it is not possible to "group schemas and users" so that a set of n users will get access to a set of m schemas DIRECTLY although roles and other mechanisms do provide shortcuts for this purpose.

6) The scope of an "Oracle user" OWNERSHIP is limited to one database - the one it is defined on?

Definitely not. The oracle user (default name 'oracle') can be the owner of n number of databases on the same machine. In this case the systemA.dbf and the systemB.dbf will belong to two different databases (ie 2 different sets of datafiles) but the OS level owner of both sets of files is the same - the OS user 'oracle'.

7) The scope of an "Oracle user" ACCESS can span multiple databases using dblinks? Therefore, a user's catalog can span multiple databases?

No. dlinks are just a short-cut mechanism for a user to LOGIN TO ANOTHER DATABASE AS A VALID USER OF THAT DATABASE and access data. Check out the create database link statement:

create database link x connect to 'scott' identified by 'tiger' using remote;

will create a dblink named x. Whenever any user accesses this dblink, oracle will connect to the database named "remote" with the username of "scott" with the password of "tiger". Scott and the current user are different users - by creating the dblink, scott has given a simple method to this user to access scott's data wutout restriction.

8) "Oracle dblinks" allow remote objects in a different "Oracle database" to be seemlessly included in a user's catalog? So, for example, issuing one Oracle request for a list of all tables that a user has access to will include any remote tables distinguished by datalink @ tablename syntax. ?
9) Or is the same request also needed on remote database inorder to discover the remote tables? If this true, how does the client app know what datalink to use if it is not hard-coded in the app?

Query no 8 is not relevant since a user's schema (or 'catalog', as you call it) cannot span multiple 'databases' ie a user's objects cannot be contained in multiple sets of datafiles belonging to different databases. For Query 9, the dblink that you need to use must be known to you, there is no shortcut. However, since your objects always lie only in your own database, there is no need for you to access your objects from other databases.

Hope this helps in clearing your doubts. I am learning MS SQL-Server currently and I have the same doubts about that database system.

Rgds,
Chetan Wagle
Senior DBA,
Cambridge Technology Partners
Bangalore, India
cwagle_at_ctp.com

The views expressed here are personal and have nothing to do with Cambridge Technology Partners.

Steven Hill wrote in message <37E68E3E.4FFEBBC9_at_yahoo.com>...
>I'm trying to reconcile some Oracle terms with some ANSI SQL & JDBC
>terminology and I'd like to know if I've made any mistakes. In other
>word, which of the following statements are false and why?
>
>1) An "Oracle database" is an instance of an Oracle DBMS, thus if there
>is more than one instance running on a computer then the computer has
>more than one database running?
>
>2) Do Oracle DBAs use the terms "Oracle database" and "Oracle server"
>interchangably? Or does "server" mean one computer with possibly
>multiple databases running on it.
>
>3) Is an "Oracle user" the same as the ANSI SQL term "catalog"? If so,
>then a Oracle user can have only one catalog? Do Oracle DBA's call a
>catalog the "user's database"?
>
>4) The Oracle docs say that a user account can only own a single schema
>which has the same name as the user name.
>Therefore, a user's catalog or user's database (identified by the user's
>name) contains one schema owned by the user (also identified by the
>user's name) and portions of other users' catalogs/schemas which the
>user has access to?
>
>5) An Oracle DBA can setup up additional user accounts, say 5, for the
>same user ie, JSmith1, JSmith2... JSmith5. with different schema access
>privileges; thus, simulating one ANSI SQL "cluster", defined as a
>"group of catalogs" and not to be confused with Oracle cluster meaning?
>
>6) The scope of an "Oracle user" OWNERSHIP is limited to one database
>-- the one it is defined on?
>
>7) The scope of an "Oracle user" ACCESS can span multiple databases
>using dblinks? Therefore, a user's catalog can span multiple databases?
>
>8) "Oracle dblinks" allow remote objects in a different "Oracle
>database" to be seemlessly included in a user's catalog? So, for
>example, issuing one Oracle request for a list of all tables that a user
>has access to will include any remote tables distinguished by datalink @
>tablename syntax. ?
>
>9) Or is the same request also needed on remote database inorder to
>discover the remote tables? If this true, how does the client app know
>what datalink to use if it is not hard-coded in the app?
>
>TIA,
>--
>Steven Hill
>
>
Received on Tue Sep 21 1999 - 04:27:43 CDT