Synopsis.
Changes to
Oracle database objects can wreak unexpected havoc on the applications that
depend on them, especially when the database objects are invalidated even for a
brief period. This article discusses some techniques that any DBA can
put into practice to limit the impact of database object changes on dependent
applications.
As a wise
old philosopher once said, “The only certainty is change.” As Oracle DBAs, we
are responsible for managing changes to database objects on a regular basis.
Depending on the stability of your IT organization and the stability of the
applications that your developers deploy and support, you may be called upon to
make changes to database objects on a daily or even hourly basis.
Since I am responsible for 24×7 support for my client’s
databases, one responsibility I take very seriously is to make sure that our
applications continue to deliver stable performance in spite of necessary
database maintenance. However, I have found that if I plan ahead, I can create
“safety blankets” or cushions that insulate the applications from the
database(s) that they access.
Safety Blankets
Use base
views to insulate applications.
In its simplest form, a base view is nothing more
than a view referencing all columns in a database table. I typically create a
base view whenever I add a new table to the database. Once the base view is
created, I also create a public synonym for the base view and then issue the
necessary object permissions to the appropriate roles.
Since
Oracle permits DML operations against the base table, I can also redirect all
DML operations to use the base view instead of the base table. Here is an
example using the EMPLOYEES table in the HR demo schema, which gives the
OLTPROLE role full DML access to the view:
CREATE OR REPLACE VIEW hr.bv_employees AS
SELECT * FROM hr.employees
/
CREATE PUBLIC SYNONYM employees FOR hr.bv_employees;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.bv_employees TO OLTPROLE;
A big
advantage of a base view is that I can now point the operations important to
applications – reading and writing data – at the base view while simultaneously
isolating the underlying table against really destructive operations like DROP
TABLE or TRUNCATE TABLE. I have also provided a margin of safety that prevents
a junior DBA or overzealous developer from dropping a critical database object
by accident or in haste.
A few
caveats: Note that if new columns are added to the base table, they are not
automatically added to the base view unless I specifically recompile the base
view. This is a double-edged sword, of course, because while it can be used to
insulate against change to the dependent objects, it also means new columns
aren’t immediately available to those objects. In addition, remember that if a
column with a constraint is left out of the base view – for example, it has a
NOT NULL constraint without a DEFAULT value provided, or it has a CHECK
constraint – and it is not included in the base view column list, an INSERT
issued against a base view will likely fail.
Use base
views to isolate application access to specific data.
Since column aliases can be
specified for a view, we can use this feature to limit a user’s view of the
data returned. Using the HR.EMPLOYEES table again as our example, let’s say I
want to limit access for the OLTPUSER role to just the columns required to
initially add a new employee to the company – in other words, just the columns
that have NOT NULL constraints:
DROP VIEW hr.bv_employees;
CREATE OR REPLACE VIEW hr.bv_employees (
empid,
fname,
lname,
email,
hire_date,
job_id)
AS
SELECT
employee_id,
first_name,
last_name,
email,
hire_date,
job_id
FROM hr.employees
/DROP PUBLIC SYNONYM employees;
CREATE PUBLIC SYNONYM employees FOR bv_employees;
GRANT SELECT, INSERT, UPDATE, DELETE on hr.bv_employees TO oltprole;
Now if I
perform DML against the EMPLOYEES base view from the OLTPUSER (which has been
granted the OLTPROLE role), I can add an employee with just the required
information:
INSERT INTO employees
VALUES (501, ‘Damien’, ‘McGillicudy’, ‘damienm@oracle.com’, TO_DATE(’12/31/1999′), ‘FI_ACCOUNT’);
COMMIT;SQL> SELECT *
2 FROM employees
3 WHERE empid >= 500
4 ORDER BY empid;EMPID FNAME LNAME
———- ——————– ————————-
EMAIL HIRE_DATE JOB_ID
————————- ——————- ———-
501 Damien McGillicudy
damienm@oracle.com 12/31/1999 00:00:00 FI_ACCOUNT
Use join
views for more complex requirements.
Using a join view carries the previous examples to
the next level: allowing an application to see information from more than one
table. Expanding on our prior example:
DROP VIEW hr.bv_employees;
CREATE OR REPLACE VIEW hr.bv_employees (
empid,
fname,
lname,
email,
hire_date,
job_id,
jobtitle,
deptname)
AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.email,
e.hire_date,
e.job_id,
j.job_title,
d.department_name
FROM
hr.employees e,
hr.jobs j,
hr.departments d
WHERE e.job_id = j.job_id
AND e.department_id = d.department_id
/
DROP PUBLIC SYNONYM employees;
CREATE PUBLIC SYNONYM employees FOR bv_employees;
GRANT SELECT, INSERT, UPDATE, DELETE on hr.bv_employees TO oltprole;
Remember that when you
create a complex view like this one, Oracle will permit you to apply DML
against one and only one base table used in the view per SQL statement
issued. In addition, INSERTs can only be made to a key-preserved table
accessed by the view. A key-preserved table is one who’s primary and unique
keys will be unique in the result set returned by the view. (In the prior
example, this would be the EMPLOYEE table.) The ALL_UPDATABLE_COLUMNS data
dictionary view bears this out:
SQL> SELECT
2 column_name,
3 updatable,
4 insertable,
5 deletable
6 FROM all_updatable_columns
7 WHERE owner = ‘HR’ AND table_name = ‘BV_EMPLOYEES’;COLUMN_NAME UPD INS DEL
—————————— — — —
EMPID YES YES YES
FNAME YES YES YES
LNAME YES YES YES
EMAIL YES YES YES
HIRE_DATE YES YES YES
JOB_ID YES YES YES
JOBTITLE NO NO NO
DEPTNAME NO NO NO
Use
packages to encapsulate functionality and standardize database access.
One of the marvelous things about
Oracle packages is their ability to encapsulate all required functionality for
a data domain into a handful of database objects. Our development teams now use
packages to describe all the public attributes (columns) and methods
(procedures and functions) for groups of base views, including all the
traditional “get” and “set” object-oriented methods an application needs to
interface with the database.
Moreover,
since a package specification describes the public interface of the package
body’s functions and procedures, it has one big advantage over traditional
stored functions and procedures: The package body can be compiled separately
from the package specification. This means that unless the signature
(i.e. the arguments or returned values) of the packaged function or procedure
has changed, there is no need to recompile the specification. This can help to
limit unnecessary recompilations of dependent objects.
