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

Thanks for excellent detailed reply. I understand/agree with all points except:

> A schema in oracle terms is a set of objects to which the user has access.
>

Joe Celko in "Instant SQL Programming" says "A schema is the data, the operators and rules of a database you have _defined_".

It seems odd that there is no term for the collection of objects (schemas) that the user either owns or has access to? I would have thought that a term like "user's database" or "user's project" would be used.

Thanks,
Steven Hill

Chetan Wagle wrote:

> 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
> >
> >

--
Steven Hill,
Computer Catalyst, Inc.
Toronto, Canada

(please take out 'remove' from my address when sending an email) Received on Tue Sep 21 1999 - 16:36:24 CDT