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

If a 'user' corresponds to the term 'schema' then what do you call the collection of schemas that a user has access to?

Are you saying that the terms "catalog" and "database" can be used interchangeably?

According to the standard you can have a SQL statement that references objects from more than one catalog. You mentioned that MS SQLServer and Sybase support multiple catalogs. So do these products support a statement such as:

select table_alias1.*, table_alias2.*
from catalog1.schema1.table1 table_alias1, catalog2.schema1.table1 table_alias2 where table_alias1.column1 = table_alias2.column1

Thanks,
Steven Hill

Joseph Weinstein wrote:

> Hi. A catalog can be viewed most precisely as a complete standalone collection
> of system tables. These define all the users, tables indices etc for a database. An
> Oracle instance is a single catalog, and you cannot have multiple catalogs within
> an Oracle instance. Some DBMSes, like Sybase and MS SQLServer can and
> do have multiple catalogs withing a single instance, so while connected to a
> given Sybase instance you can change your database context to different daatabases,
> each of which have an independent and complete list of tables, users etc.
> A 'user' corresponds to the term 'Schema'.
>
> Steven Hill wrote:
>
> > 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 Mon Sep 20 1999 - 19:49:08 CDT