What’s in an Oracle Schema?

A fairly common question on Oracle Q&A forums is one where
the original poster (OP) asks how to identify schemas within a database,
usually accompanied by some qualifying reason such as “my management needs to
know how many schemas we have.” Many times, a mostly correct, but really quite
misleading answer is given telling the OP to query owner names from DBA_TABLES.
When is this answer correct? Only for those schemas (owners) who own tables.
Here’s where you can run into a misunderstanding of concepts or of the
functions/purposes a schema can support. This is analogous to a stopped watch
being correct in time at least twice a day. The rest of the day it is wrong.

First of all, who says a schema must (as in always) contain
one or more tables? If you think that is the always the case, you are wrong.
You may have created scores of databases back in the day, with each schema
containing tables and other objects, and of course every schema has tables you
say. But, for every schema owning a table, you can find just as many if not
more schemas in applications today where the schema is entirely bereft of
tables. Case in point: Oracle’s flagship ERP application known as E-Business
Suite has a built-in schema named APPLSYSPUB. In Release 12 (as an example),
all it has are synonyms. What does the APPLSYSPUB account do? Its main purpose
is to control user access to EBS, somewhat like a gateway user account. Looked
at another way, good luck trying to logon to EBS without this account being in
place.

Related to application security is application access. It is
very common to see user accounts where everything the use can see is based on
views and synonyms. These accounts typically have identifying names such as
VIEWONLY, README, READER, something_USER, something_REPORTS, and so on. In
addition to the views and synonyms, another common object found here is a
database link. No point in creating a public database link when a private one
will do. These public accounts are meant for fairly wide open access by all
users within an organization. This is obviously not a good approach if dealing
with sensitive personal data, but for general application/process reporting,
you have to weigh the trade-off between tight security and
convenience/practicality.

Related to a viewonly type of account is something similar
to what EBS uses. User access does not have to be strictly controlled or
managed by Oracle (with the exception of the gateway account). It is not
uncommon to see applications where user access is managed within the
application, and the application is based on an entirely different schema. The
connection workflow is then like this: you sign on via a portal (similar to
what can be done inside Application Server and a “web” account), enter your
username and password via a form (HTML in this case, not the Oracle type), and
your credentials are validated against a security table within the main
application. The portal account essentially passes you on to further pages once
you have been validated/authenticated. The portal account is like a firewall
between the outside world and the application.

What about functionality? Suppose your application is heavy
into Java. Instead of spreading Java stored procedures across several schemas,
you can create one schema – again, with no tables whatsoever – used to manage
all of your Java stored procedures.

Yet another example of a non-table owning schema can be
found in external connections. How common is it to see connections from
databases other than Oracle into Oracle? (Answer: very, and it works both
ways.) Let’s say you have a requirement to insert data into a table within a
SQL Server database. The insert statement is based on a select statement using
an Oracle database as the linked server target. The linked server in MSSQL is
much like a database link in Oracle in that you have to provide a username and
password (among other items). As a security measure, this would be a perfect
opportunity to use a README account in Oracle. The README account has select
privileges on the Oracle tables of interest. The query from MSSQL, using the
correct T-SQL syntax (uppercase and the two consecutive periods, keep this mind
when your statement won’t validate), simply references the Oracle tables just
like you would using database links in Oracle.

Wait a minute – all of this can be done on the Oracle side
with no objects at all, so how is this README account a schema? So far, it is
not, but you’re going to make it one as soon as one of your MSSQL queries
involves a join of two or more tables (or views and combinations thereof) in
Oracle. The reason why you will do this? All you have to see is the MSSQL
version of an execution plan using all of the tables versus one that pulls from
a view on the Oracle side. Without the view, MSSQL will pull from each table, and
then do the joining/sorting on its end, which can take a very long time when
compared to letting Oracle do the joining/sorting via a view on its end.

Coming back to the original question, the simplest, and most
correct, answer is to select distinct owner from DBA_OBJECTS, and optionally
exclude the obvious no-brainers such as SYS and SYSTEM. The results of this
query provide the definitive answer. Claims that it is rare to find schemas
with no tables are wrong. It is not rare at all, but in fact, quite common.

In Closing

The simplest definition of a schema can be found in the
glossary of all releases. In 11g, the Concepts manual states a schema is
a:

Collection of database objects, including logical
structures such as tables, views, sequences, stored procedures, synonyms,
indexes, clusters, and database links. A schema has the name of the user who
controls it.

Anything in that definition about having to contain tables?
No, so don’t limit your answer to a narrow and incorrect interpretation of
what’s in a schema.

»


See All Articles by Columnist
Steve Callan

Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles