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

At 08:49 PM 9/20/99 -0400, you wrote:
>If a 'user' corresponds to the term 'schema' then what do you call the collection of
>schemas that a user has access to?

These are just the collection of schemas that a user has access to. If the permissions in the database are such that one user can refer to another user's tables, then so be it. The SQL will have to explicitly qualify the schema for any object not in the current (users) schema. In MS SQLServer, a fully qualified name is:

select * from <catalog>.<schema>.<tablename>

If catalog is left out, it is assumed to be the current catalog/database. if schema is left out, it is assumed to be the current users, but if no such table belongs to the user, then the system schema is checked.

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

Yes. In the case of Oracle, it is also synonymous with the Oracle instance. An Oracle instance contains only one set of system tables (a catalog). A single MS SQLServer instance can be subdivided into numerous autonomous databases, each with it's own set of system tables and memory space. Each of these is called a database. An MS SQLServer user can select from tables in any or all databases to which he has permission, by fully qualifying the query.

>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

Yes.

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

--
PS: Hey folks, we're hiring Java engineers for our WebLogic

    Engineering group in downtown S.F. Send me your resume.

                    The Weblogic Application Server from BEA
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
               http://weblogic.beasys.com/press/awards/index.htm