Pro Oracle Database 11g Administration (38 page)

196

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

SQL> update emp_dept_v set emp_name = 'Jon' where emp_name = 'John'; However, statements that result in updating the DEPT table’s columns aren’t allowed. The next statement attempts to update a column in the view that maps to the DEPT table: SQL> update emp_dept_v set dept_name = 'HR West' where dept_name = 'HR'; Here’s the resulting error message that’s thrown:

ORA-01779: cannot modify a column which maps to a non key-preserved table To summarize, a join view can select from many tables, but only one of the tables in the join view is key preserved. The primary-key and foreign-key relationships of the tables in the query determine which table is key-preserved, and not the data returned by the view.

Creating an INSTEAD OF Trigger

An INSTEAD OF trigger on a view instructs Oracle to execute PL/SQL code instead of using the DML

statement. The INSTEAD OF trigger allows you to modify the underlying base tables in ways that you can’t with regular join views.

I’m not a huge fan of INSTEAD OF triggers. In my opinion, if you’re considering using them, you should rethink how you’re issuing DML statements to modify base tables. Maybe you should allow the application to issue INSERT, UPDATE, and DELETE statements directly against the base tables instead of trying to build PL/SQL INSTEAD OF triggers on a view.

Think about how you’ll maintain and troubleshoot issues with INSTEAD OF triggers. Will it be difficult for the next DBA to figure out how the base tables are being modified? Will it be easy for the next DBA or developer to make modifications to the INSTEAD OF triggers? When an INSTEAD OF trigger throws an error, will it be obvious what code is throwing the error and how to resolve the problem?

Having said that, if you determine that you require an INSTEAD OF trigger on a view, use the INSTEAD

OF clause to create it, and embed it in it the required PL/SQL. This example creates an INSTEAD OF trigger on the EMP_DEPT_V view (created in the previous section):

create or replace trigger emp_dept_v_updt

instead of update on emp_dept_v

for each row

begin

update emp set emp_name=UPPER(:new.emp_name)

where emp_id=:old.emp_id;

end;

/

Now, when an update is issued against EMP_DEPT_V, instead of the DML being executed, Oracle intercepts the statement and runs the INSTEAD OF PL/SQL code. For example: SQL> update emp_dept_v set emp_name='Jonathan' where emp_id = 10;

1 row updated.

Now you can verify that the trigger correctly updated the table by selecting the data: 197

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

SQL> select * from emp_dept_v;

EMP_ID EMP_NAME DEPT_NAME DEPT_ID

---------- --------------- --------------- ----------

10 JONATHAN IT 2

20 Bob HR 1

30 Craig IT 2

40 Joe SALES 3

50 Jane HR 1

60 Mark IT 2

This code is a simple example, but it illustrates that you can have PL/SQL execute instead of the DML that was run on the view. Again, be careful when using INSTEAD OF triggers; be sure you’re confident that you can efficiently diagnose and resolve any related issues that may arise.

Modifying a View Definition

If you need to modify the SQL query on which a view is based, then either drop and re-create the view or use the CREATE OR REPLACE syntax as in the previous examples. For instance, to add the REGION column to the SALES_ROCKIES view, run the following to replace the existing view: create or replace view sales_rockies as

select sales_id, amnt, state, region

from sales

where state in ('CO','UT','WY','ID','AZ')

with read only;

The advantage of using the CREATE OR REPLACE method is that you don’t have to reestablish access to the view for users with previously granted permissions.

The alternative to CREATE OR REPLACE is to drop and re-create the view with the new definition. If you drop and re-create the view, you must re-grant privileges to any users or roles that were previously granted access to the dropped and re-created object. For this reason, I almost never use the drop and recreate method when altering the structure of a view.

■
Note
The ALTER VIEW command is used to modify a view’s constraint attributes. You can also use the ALTER

VIEW command to recompile a view.

Displaying the SQL Used to Create a View

Sometimes, when you’re troubleshooting issues with the information a view returns, you need to see the SQL query on which the view is based. Use the following script to display the text associated with a particular view for a user:

select

view_name

,text

from dba_views

where owner = upper('&owner')

and view_name like upper('&view_name');

198

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

Renaming a View

There are a couple of good reasons to rename a view. You may want to change the name so that it better conforms to standard, or you may want to rename a view before dropping it so that you can better determine whether it’s in use.

Use the RENAME statement to change the name of a view. This example renames the INV view to INV_OLD:

SQL> rename inv to inv_old;

You should see this message:

Table renamed.

Dropping a View

Before you drop a view, consider renaming it. If you’re certain that a view isn’t being used anymore, then it makes sense to keep your schema as clean as possible and drop any unused objects. Use the DROP VIEW

statement to drop a view:

SQL> drop view inv_v;

Keep in mind that when you drop a view, any dependent views, materialized views, and/or synonyms become invalid.

Managing Synonyms

Synonyms provide a mechanism to create an alternate name for an object. For example, say USER1 is the currently connected user, and USER1 has select access to USER2’s EMP table. Without a synonym, USER1

must select from USER2’s EMP table as follows:

SQL> select * from user2.emp;

With a synonym, USER1 can do the following:

SQL> create synonym emp for user2.emp;

SQL> select * from emp;

You can create synonyms for the following types of database objects:

• Tables

• Views or object views

• Other synonyms

• Remote objects via a database link

• PL/SQL packages, procedures, and functions

• Materialized views

• Sequences

• Java class schema object

• User-defined object types

200

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

Creating a synonym that points to another object eliminates the need to specify the schema owner or name of the object. This lets you create a layer of abstraction between an object and the user, often referred to as
object transparency
. Synonyms allow you to transparently manage objects separately from the users who access the objects. You can also seamlessly relocate objects to different schemas or even different databases. The application code that references the sequences doesn’t need to change—only the definition of the synonym.

■
Tip
You can use synonyms to set up multiple application environments within one database. Each environment has its own synonyms that point to a different user’s objects, allowing you to run the same code against several different schemas within one database. For example, you may do this because you can’t afford to build a separate box or database for development, testing, quality assurance, production, and so on.

Creating a Synonym

Use the CREATE SYNONYM command to create an alias for another database object. The following example creates a synonym for a table named INV that’s owned by the INV_MGMT user: SQL> create or replace synonym inv for inv_mgmt.inv;

After you’ve created the INV synonym, you can operate on the INV_MGMT.INV table directly. If select access has been granted to the INV_MGMT.INV table, you can now select by referencing the synonym INV:

SQL> select * from inv;

The creation of the synonym doesn’t create the privilege to access an object. Such privileges must be granted separately, usually before you create the synonym.

By default, when you create a synonym, it’s a private synonym. This means it’s owned by the user who created the synonym, and other users can’t access the synonym unless they’re granted the appropriate object privileges.

Creating Public Synonyms

You can also define a synonym to be public (see the prior section for a discussion of private synonyms), which means any user in the database has access to the synonym. Sometimes, an inexperienced DBA does the following:

SQL> grant all on books to public;

SQL> create public synonym books for inv_mgmt.books;

Now, any user who can connect to the database can perform any INSERT, UPDATE, DELETE, or SELECT

operation on the BOOKS table that exists in the INV_MGMT schema. You may be tempted to do this so you don’t have to bother setting up individual grants and synonyms for each schema that needs access. This is almost always a bad idea. There are a few issues with using public synonyms: 201

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

• Troubleshooting can be problematic if you’re not aware of globally defined (public) synonyms.

• Applications that share one database can have collisions on object names if multiple applications use public synonyms that aren’t unique within the database.

• Security should be administered as needed, not on a wholesale basis.

I usually try to avoid using public synonyms. However, there may be scenarios that warrant their use. For example, when Oracle creates the data dictionary, public synonyms are used to simplify the administration of access to internal database objects. To display any public synonyms in your database, run this query:

select owner, synonym_name

from dba_synonyms

where owner='PUBLIC';

Dynamically Generating Synonyms

Sometimes it’s useful to dynamically generate synonyms for all tables or views for a schema that needs private synonyms. The following script uses SQL*Plus commands to format and capture the output of a SQL script that generates synonyms for all tables within a schema:

CONNECT &&master_user/&&master_pwd.@&&tns_alias

--

SET LINESIZE 132 PAGESIZE 0 ECHO OFF FEEDBACK OFF

SET VERIFY OFF HEAD OFF TERM OFF TRIMSPOOL ON

--

SPO gen_syns_dyn.sql

--

select 'create or replace synonym ' || table_name ||

' for ' || '&&master_user..' ||

table_name || ';'

from user_tables;

--

SPO OFF;

--

SET ECHO ON FEEDBACK ON VERIFY ON HEAD ON TERM ON;

Look at the &&master_user variable with the two dots appended to it in the SELECT statement: what is the purpose of double-dot syntax? A single dot at the end of an ampersand variable instructs SQL*Plus to concatenate anything after the single dot to the ampersand variable. When you place two dots together, that tells SQL*Plus to concatenate a single dot to the string contained in the ampersand variable.

Displaying Synonym Metadata

The DBA/ALL/USER_SYNONYMS views contain information about synonyms in the database. Use the following SQL to view synonym metadata for the currently connected user: select

synonym_name, table_owner, table_name, db_link

from user_synonyms

order by 1;

202

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

The ALL_SYNONYMS view displays all private synonyms, all public synonyms, and any private synonyms owned by different users for which your currently connected user has select access to the underlying base table. You can display information for all private and public synonyms in your database by querying the DBA_SYNONYMS view.

The TABLE_NAME column in the DBA/ALL/USER_SYNONYMS views is a bit of a misnomer because TABLE_NAME can reference many types of database objects, such as another synonym, view, package, function, procedure, materialized view, and so on. Similarly, TABLE_OWNER refers to the owner of the object (and that object may not necessarily be a table).

When you’re diagnosing data-integrity issues, sometimes you first want to identify what table or object is being accessed. You can select from what appears to be a table, but in reality it may be a synonym that points to a view that selects from a synonym which in turn points to a table in a different database.

The following query is often a starting point for figuring out whether an object is a synonym, a view, or a table:

select

owner

,object_name

,object_type

,status

from dba_objects

where object_name like upper('&object_name%');

Notice that using the percent-sign wildcard character in this query allows you to enter the object’s partial name. Therefore, the query has the potential to return information regarding any object that partially matches the text string you enter.