What's in an Oracle Schema?
February 25, 2009
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. Heres 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: Oracles 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.) Lets 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 wont 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 youre 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.
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 dont limit your answer to a narrow and incorrect interpretation of whats in a schema.