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 24x7 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.
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.
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', 'firstname.lastname@example.org', 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 email@example.com 12/31/1999 00:00:00 FI_ACCOUNT
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
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.