Oracle Safety Blankets: Insulating Your Applications Against Database Changes
July 29, 2003
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.
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.
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.
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.
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.