Synonyms

A synonym is merely a way of assigning an alias with which users can reference a table or view. This makes it easier to access tables that you do not own. When you own a table, you use its name in your SQL queries. When you want to access a table owned by another user, you need to specify both the owner of the table and the table name (in the owner.table_name format). When you reference tables located in a different database using SQL*Net, the format can be rather complicated. Synonyms simplify this access because you can create a simple synonym that contains the details of a complex reference string (see Figure 12.4).

Figure 12.4.  Synonyms.

There are two types of synonyms—private and public. Private synonyms are those that you create for your own personal use. Public synonyms are those that you create that any user in the database can use. Obviously, from a DBA point of view, you usually want to restrict the number of users who can create public synonyms. You usually want to be even more restrictive with the number of users who can drop public synonyms. There are three primary system privilege sets with which the DBA is concerned related to synonyms:

•  CREATE SYNONYM (which also allows you to drop private synonyms)

•  CREATE PUBLIC SYNONYM

•  DROP PUBLIC SYNONYM

Finally, it is interesting to consider what you get when you issue a query that goes against an object name that states the following: You own a table with that name, you have a private synonym with that name, and there is also a public synonym with that name. I had to try this out once to figure out what Oracle would do. After I got the results of this experiment, it made sense to me. So, here is the order in which Oracle accesses objects that have the same name in the database:

•  If you own a table with a particular name, you always access your table on a query to that object name.

•  If you do not own a table with that name, but have a private synonym with that name, you access the object associated with that private synonym.

•  Finally, if you have neither a table nor a private synonym with the object name you are searching for, you access the object specified by the public synonym.

Stored Procedures and Packages

As mentioned earlier in this chapter, stored procedures are a way of storing software within the Oracle database. Basically, anything that you can develop in a PL/SQL script can be saved as an Oracle stored procedure. A collection of stored procedures can be saved as a package in the database. Stored procedures can present several interesting advantages for Oracle developers:

•  Oracle security mechanisms, such as roles and grants, can be used to protect both the data and the software. This also provides one place of control for security administration.

•  When you execute a stored procedure, you execute it as if you were the person who created it and not with the privileges that you would normally have. This fits well with certain environments, which need to implement security features in the software, such as audit trails and special validity checking. The DBA can grant write access only to the user who creates the stored procedures. Users who are authorized to perform write operations are not given that permission in their Oracle accounts. Instead, they are given access to the stored procedures that must be executed (and hence the software security features enforced) to perform the write operations. If they try issuing SQL commands to write to these tables from SQL*Plus, they are denied access.

•  Another advantage of stored procedures is that Oracle stores both the source code and a parsed version of the software. This saves Oracle the time of having to parse the statements each time as it would for SQL queries received from external software applications.

There are two problems with stored procedures. First, all stored procedures are stored in the system tablespace, usually thought of as sacred ground for use only by the DBA. However, the Oracle developers do not agree. Therefore, you have to let non-DBA software be placed in your most special tablespace. The other problem is that the software has to be written in PL/SQL. PL/SQL gets the job done; however, it is difficult to debug and does not support normal input/output operations. Stored procedures are very useful if you are willing to live with these two annoyances.

Stored procedures require special system privileges for creation (as you would expect with something that can be this powerful and that has to be placed in the system tablespace). Without getting into the details that are of concern to software developers, you need to grant CREATE PACKAGE, CREATE PROCEDURE, DROP PACKAGE, and/or DROP PROCEDURE to users who will be developing stored procedures in your system. Chapter 17 will cover stored software in greater detail.

Clusters

Recall that a cluster is a group of tables that you want to store together because they contain a number of common columns and are often joined together in queries. I have never used clusters. They were common in Oracle6, but the basic data storage algorithm for Oracle8 is a lot smarter that in Oracle6, and it will arrange tables in an efficient manner without creating clusters. One nice feature about clusters is that users have no idea whether the tables that they are accessing are in a cluster. Only the developers and DBAs know which tables are in clusters.

The basic Oracle references provide a good description of the details of setting up clusters. They also go into more detail about when you might use them. However, for purposes of this book, it is enough for you to know what they are. If you have performance problems with queries that always join together a few tables that have common columns, you may consider experimenting with clusters. Otherwise, you may never see a cluster. (Although many of the Oracle internal tables are supposed to use clusters, you may never deal with these tables directly because that is what you purchased the RDBMS software to do for you.)