OracleDBUtils

Helpful PL/SQL utilities for Oracle database. Written and tested for Oracle 10.2 (should work for later versions, some parts may work for earlier versions too).

Contents:

p_admin
p_utils
p_stack
utl_call_stack
String aggregation
Links to other packages from various authors

Released into public domain.

p_admin

A package for sessions and jobs control. If jobs are intensively running all the time, it's hard to compile source code used by them (compilation would hang). So we need to stop jobs before this process. But inner database methods (such as "alter system kill session" or marking job as broken) may take too long while waiting for resources to be disposed or waiting for the job to be finished. A detailed description of various methods of killing sessions can be found here. This package offers methods for immediate jobs and sessions termination. Usually this task is performed by DBAs, not by developers. But sometimes it may ease the process of frequent compilations for both DBAs and developers (by giving more privileges for latter ones), e.g. on test databases.

procedure killSession( tSid in number );

This procedure takes session SID (from view V$SESSION) as argument and kills a corresponding session by calling orakill.exe (on Windows server) or kill -9 (on UNIX machines). The call is performed via Java stored procedure (see runCommand.sql), which source code is a modified version of Host class available here. Only current schema sessions killing is allowed (you can modify the source code to avoid this restriction). So it's safe to grant execute on this package to any schema. The owner of this package is allowed to kill any schema session (except SYS).

procedure killAllSessions;

This procedure just calls killSession for all sessions available at V$SESSION view ignoring system sessions and a current one. It's useful for releasing locks on database objects.

procedure killDevelopersSessions;

Kill sessions of all users logged in with their accounts (sessions, for which OSUSER differs from 'SYSTEM'). Modify it not to kill sessions of application server such as Tomcat by adding corresponding user name (if it's not NT AUTHORITY\SYSTEM) to exceptions inside this procedure.

procedure killUserSessions;

Kill sessions of current user (OSUSER in V$SESSION). It's useful if your query takes too long to finish and cannot be killed immediately by "alter system kill session".

procedure killUserTestSessions;

Kill debug (Test Window) sessions of current user when logged in via PL/SQL Developer.

procedure killJob( tJob in number );

Stop DBMS_JOB execution by a given identifier (JOB from view USER_JOBS).

procedure pauseAllJobs( tMin in number default 1 / 144, tMax in number default null, tIncr in number default null );

Consider the following example:

Current time is 10:00.
Job #1 is running.
Job #2 is scheduled to run at 10:05.
Job #3 is scheduled to run at 10:30.

We call pauseAllJobs( tMin => 1 / 144 ). So we want to pause all jobs for 10 minutes. What happens:

Job #1 is killed and scheduled to run at 10:10.
Job #2 is scheduled to run at 10:10.
Job #3 is not touched and still scheduled to run at 10:30.

It lets us a time gap to compile source code. And we are sure that no job is executing and no job would start execution during the nearest 10 minutes.

We call pauseAllJobs( tMax => 1 / 144 ). The result is:

Job #1 is killed and scheduled to launch immediately (at least not later than 10:10).
Job #2 is not touched and scheduled to run at 10:05.
Job #3 is scheduled to run at 10:10.

Use it if you need to launch all (or some - see further methods) jobs in nearest time. Say, you have corrected an error in source file used by all jobs and need to recalculate all values filled by jobs.

After call to pauseAllJobs( tIncr => 1 / 144 ) we get:

Job #1 is killed and scheduled to run at 10:10.
Job #2 is scheduled to run at 10:15.
Job #3 is scheduled to run at 10:40.

It just shifts the existing NEXT_DATE (from USER_JOBS) if tIncr is set.

So, more formally, this method kills all jobs and schedules them to launch at min( sysdate + tMax, max( sysdate + tMin, NEXT_DATE + tIncr ) ).

procedure pauseJob( tJob in number, tMin in number default 1 / 144, tMax in number default null, tIncr in number default null );

This method pauses (kills and reschedules) a job by a given identifier.

procedure pauseJobsLike( tLikeCondition in varchar2, tMin in number default 1 / 144, tMax in number default null, tIncr in number default null, tJob in number default null );

This is the most generic method of jobs delaying. It takes a mask as argument (tLikeCondition). This mask affects WHAT column of USER_JOBS view. All jobs that suit the mask condition would be killed (if needed) and rescheduled. Example:

pauseJobsLike( 'my_schema.my_package.%' );

This call will guarantee that all jobs starting from 'my_schema.my_package.' would wait at least 10 minutes before next run. The condition is simple: where WHAT like tLikeCondition. So wildcards like '%' and '_' are acceptable.

function getSessionId return number;

This method returns SID of a current session. This call is equivalent to sys_context( 'userenv', 'sessionid' ).

function getJobId return number;

This method returns an identifier of the current executing job. This is the field JOB from USER_JOBS.

Installation notes:

First, compile runCommand.sql under sys. Grant appropriate Java right to access files if needed as described here. Example for *NIX environment:

begin
  dbms_java.grant_permission( 'Schema_that_owns_runCommand', 'SYS:java.io.FilePermission', '/bin/sh', 'execute' );
end;

It may require additional permission which can be set like this:

begin
  dbms_java.grant_policy_permission( 'Schema_that_owns_runCommand', 'SYS', 'java.io.FilePermission', '*' );
end;

Run it under sys or with JAVA_ADMIN role granted. Use the following snippet to remove this policy permission after file execution permission is granted:

declare
  tSeq number;
begin
  select SEQ
  into tSeq
  from DBA_JAVA_POLICY
  where KIND = 'GRANT'
    and GRANTEE = 'Schema_that_owns_runCommand'
    and TYPE_SCHEMA = 'SYS'
    and TYPE_NAME = 'oracle.aurora.rdbms.security.PolicyTablePermission'
    and NAME = '0:java.io.FilePermission#*';
  dbms_java.disable_permission( tSeq );
  dbms_java.delete_permission( tSeq );
end;

It's not recommended to grant rights on runCommand procedure to someone else except sys. Then, compile p_admin.sql under sys and grant rights/add synonym to target user schema. The target user may also need an access to V$SESSION view to find SID of hanged session.

p_utils

A package for various tasks: collections manipulation, numeric utilities.

function numberToChar( tNumber in number ) return varchar2;

Standard to_char( 0.23 ) returns '.23' (zero omitted), this function adds zero when needed ('0.23' for the example above). A detailed explanation of the problem on StackOverflow. Examples:

with SAMPLES as (
  select -4.67 as VALUE from dual union all
  select -4 from dual union all
  select -0.25 from dual union all
  select 0.25 from dual union all
  select 0.5 from dual union all
  select 0 from dual union all
  select 1 f

OracleDBUtils

Install / Use

README

OracleDBUtils

p_admin

p_utils