Oracle objects maintenance with SQL scripts

September 28, 2010 by Leave a comment 

On database environments there should be a strict change management policy with agreed rules between database development teams and DBAs on how to maintain database objects.

Imagine you have a large number of Oracle database schemas on one database instance running many different applications. Maintaining database objects on such an environment can be tricky. Below I give some tips how to do just that by using SQL scripts and the SQL*Plus utility.

What Oracle user to use for running SQL scripts

I suggest using an empty DBA user (not sys or system). That will avoid Oracle error ORA-00942 (table or view does not exist) if the connected user does not have permission on the current schema object.

How to use a proper schema name

It is a time consuming process for a DBA and developers to put the schema name in front of the objects from that schema where executing maintenance processes. This alias process eliminates the same Oracle error ORA-00942.

Using CURRENT_SCHEMA session parameter offers a convenient way to perform operations on objects in a schema other than that of the current user, without having to qualify the objects with the schema name. See the SQL below:

alter session set current_schema=schema-name;

The CURRENT_SCHEMA parameter changes the current schema of the session to a specified schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.

SQL*Plus spooling

Better to not use spooling in the SQL script itself but let a DBA to do that task. They should have a proper environment setup for running the developerment scripts.

Case sensitivity

Make sure your SQL script names are case sensitive since they might be run on Unix environment!

Freeze the application during SQL script execution

Do NOT run any DML/DDL statements, batch jobs against affected database objects during possible script execution time.

A new feature of Oracle 11gR2 Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

Complication errors

To see possible compilation errors for a stored PL/SQL code put the following SQL*Plus command just after it:

show err object_type schema.object_name

SQL Example:

show err procedure hr.proc1

Recompile at the end

If you want all invalid schema objects to be recompiled after the SQL script run, put the following SQL statement at the end of your SQL script:

exec sys.utl_recomp.recomp_serial('schema_name');

Common SQL script mistakes

– Do not forget using “/” at the end of any stored PL/SQL code

– If you have a separate index tablespace, specify it explicitly for your indexes and PRIMARY KEY / UNIQUE constraints

– COMMIT is not required after a DDL (Data Definition Language) statement. DDL itself produces an implicit commit

– If you want to insert ‘&’ into a table – read manual (one way is using “set define off” at the top of the SQL script, another one is to use ESCAPE clause)

– Don’t forget to change your current schema settings after your operations on other schemas. Use the query below to get your current_schema status:

select sys_context(‘userenv’,'current_schema’) from dual;

Do functional tests before giving SQL scripts out for execution

Follow your agreed change management process

In summary I’d like to mention that the database object maintenance is not only the technique but also the robust change management process that all the involved parties should follow. Only both can ensure your database, data and application is stable and at maximum availability.

Enjoyed this article? Please share it with others using the social site of your choice:

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email