How To Install Dbms_lock Package

Installing RE Loader. To install RE Loader, perform the procedures in these sections: Granting Execute Permission for dbmslock. Granting Write Permission to the DM. Installing the RE Loader Package. Creating Your RE Loader Database Partitions. Returning DM Permissions to their Original Values. User-named locks with DBMSLOCK The application I’m working on involves a number of views that provide an interface between two databases that have quite different models. The views implement inserts, updates and deletes via Instead-of triggers.

Oracle Lock Management services for your applications are available through procedures in the DBMS_LOCK package. You can request a lock of a specific mode, give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it.

Because a reserved user lock is the same as an Oracle lock, it has all the functionality of an Oracle lock, such as deadlock detection. Be certain that any user locks used in distributed transactions are released upon COMMIT, or an undetected deadlock may occur.

User locks never conflict with Oracle locks because they are identified with the prefix 'UL'. You can view these locks using the Enterprise Manager lock monitor screen or the appropriate fixed views. User locks are automatically released when a session terminates.

The lock identifier is a number in the range of 0 to 1073741823.

Some uses of user locks:

• Providing exclusive access to a device, such as a terminal
• Providing application-level enforcement of read locks
• Detecting when a lock is released and cleanup after the application
• Synchronizing applications and enforcing sequential processing

Requirements

DBMS_LOCK is most efficient with a limit of a few hundred locks per session. Oracle strongly recommends that you develop a standard convention for using these locks in order to avoid conflicts among procedures trying to use the same locks. For example, include your company name as part of your lock names.

Security

There might be operating system-specific limits on the maximum number of total locks available. This must be considered when using locks or making this package available to other users. Consider granting the EXECUTE privilege only to specific users or roles.

A better alternative would be to create a cover package limiting the number of locks used and grant EXECUTE privilege to specific users. An example of a cover package is documented in the DBMSLOCK.SQL package specification file.

Viewing and Monitoring Locks

Oracle provides two facilities to display locking information for ongoing transactions within an instance:

Constants

These are the various lock modes (nl -> 'NuLl', ss -> 'Sub Shared', sx -> 'Sub eXclusive', s -> 'Shared', ssx -> 'Shared Sub eXclusive', x -> 'eXclusive').

A sub-share lock can be used on an aggregate object to indicate that share locks are being aquired on sub-parts of the object. Similarly, a sub-exclusive lock can be used on an aggregate object to indicate that exclusive locks are being aquired on sub-parts of the object. A share-sub-exclusive lock indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks.

Lock Compatibility Rules

When another process holds 'held', an attempt to get 'get' does the following:

Table 18-1 Lock Compatibility

The constant maxwait waits forever.

Summary of Subprograms

Table 18-2 DBMS_LOCK Package Subprograms

ALLOCATE_UNIQUE procedure

This procedure allocates a unique lock identifier (in the range of 1073741824 to 1999999999) given a lock name. Lock identifiers are used to enable applications to coordinate their use of locks. This is provided because it may be easier for applications to coordinate their use of locks based on lock names rather than lock numbers.

If you choose to identify locks by name, you can use ALLOCATE_UNIQUE to generate a unique lock identification number for these named locks.

The first session to call ALLOCATE_UNIQUE with a new lock name causes a unique lock ID to be generated and stored in the dbms_lock_allocated table. Subsequent calls (usually by other sessions) return the lock ID previously generated.

A lock name is associated with the returned lock ID for at least expiration_secs (defaults to 10 days) past the last call to ALLOCATE_UNIQUE with the given lock name. After this time, the row in the dbms_lock_allocated table for this lock name may be deleted in order to recover space. ALLOCATE_UNIQUE performs a commit.

Syntax

Parameters

Table 18-3 ALLOCATE_UNIQUE Procedure Parameters

Errors

ORA-20000, ORU-10003: Unable to find or insert lock <lockname> into catalog dbms_lock_allocated.

Exceptions

None.

REQUEST function

This function requests a lock with a given mode. REQUEST is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

Syntax

The current default values, such as X_MODE and MAXWAIT, are defined in the DBMS_LOCK package specification.

Parameters

Table 18-4 REQUEST Function Parameters

Return Values

Table 18-5 REQUEST Function Return Values

Exceptions

None.

CONVERT function

This function converts a lock from one mode to another. CONVERT is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

Syntax

Parameters

Table 18-6 CONVERT Function Parameters

Return Values

Table 18-7 CONVERT Function Return Values

Exceptions

None.

RELEASE function

This function explicitly releases a lock previously acquired using the REQUEST function. Locks are automatically released at the end of a session. RELEASE is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

Syntax

Parameters

Table 18-8 RELEASE Function Parameter

Return Values

Table 18-9 RELEASE Function Return Values

Exceptions

None.

SLEEP procedure

This procedure suspends the session for a given period of time.

Syntax

Parameters

Table 18-10 SLEEP Procedure Parameters

Example

This Pro*COBOL precompiler example shows how locks can be used to ensure that there are no conflicts when multiple people need to access a single device.

Print Check

Any cashier may issue a refund to a customer returning goods. Refunds under $50 are given in cash; anything above that is given by check. This code prints the check. The one printer is opened by all the cashiers to avoid the overhead of opening and closing it for every check. This means that lines of output from multiple cashiers could become interleaved if we don't ensure exclusive access to the printer. The DBMS_LOCK package is used to ensure exclusive access.

CHECK-PRINT

Get the lock 'handle' for the printer lock:

Lock the printer in exclusive mode (default mode):

We now have exclusive use of the printer, print the check:

Unlock the printer so other people can use it:

To download latest version of utPLSQL from github on both Unix/Linux as well as Windows machines use the below snippets.

Unix/Linux

You may download with a one-liner if that is more convenient.

Windows

To run the script on windows you will need PowerShell 3.0 or above.You will also need .NET 4.0 Framework or above.

To check the framework version execute the following query:

Additionally you may retrieve more information about your environment by executing the following query:

The utPLSQL may be installed on any supported version of Oracle Database see

• 11g R2
• 12c
• 12c R2
• 18c
• 19c

utPLSQL can be installed with DDL trigger, to enable tracking of DDL changes to your unit test packages.This is the recommended installation approach, when you want to compile and run unit test packages in a schema containing huge amount of database packages (for example Oracle EBS installation schema).The reason for having DDL trigger is to enable in-time annotation parsing for utPLSQL.Without DDL trigger, utPLSQL needs to investigate your schema objects last_ddl_timestamp each time tests are executed to check if any of DB packages were changed in given schema and if they need scanning for annotation changes.This process can be time-consuming if DB schema is large.

The headless scripts accept three optional parameters that define:

• username to create as owner of utPLSQL (default ut3)
• password for owner of utPLSQL (default XNtxj8eEgA6X6b6f)
• tablespace to use for storage of profiler data (default users)

The scripts need to be executed by SYSDBA, in order to grant access to DBMS_LOCK and DBMS_CRYPTO system packages.

Note:

Grant on DBMS_LOCK is required only for installation on Oracle versions below 18c. For versions 18c and above, utPLSQL uses DBMS_SESSION.SLEEP so access to DBMS_LOCK package is no longer needed.

Note:

The user performing the installation must have the ADMINISTER DATABASE TRIGGER privilege. This is required for installation of trigger that is responsible for parsing annotations at at compile-time of a package.

Note:

When installing with DDL trigger, utPLSQL will not be registering unit tests for any of oracle-maintained schemas.For Oracle 11g following users are excluded:ANONYMOUS, APPQOSSYS, AUDSYS, DBSFWUSER, DBSNMP, DIP, GGSYS, GSMADMIN_INTERNAL, GSMCATUSER, GSMUSER, ORACLE_OCM, OUTLN, REMOTE_SCHEDULER_AGENT, SYS, SYS$UMF, SYSBACKUP, SYSDG, SYSKM, SYSRAC, SYSTEM, WMSYS, XDB, XS$NULL

For Oracle 12c and above the users returned by below query are excluded by utPLSQL:

Installation without DDL trigger

To install the utPLSQL into a new database schema and grant it to public, execute the script install_headless.sql as SYSDBA.

Example invocation of the script from command line:

Invoking script with parameters:

Installation with DDL trigger

To install the utPLSQL into a new database schema and grant it to public, execute the script install_headless_with_trigger.sql as SYSDBA.

Example invocation of the script from command line:

Invoking script with parameters:

It is highly recommended to install utPLSQL in it's own schema. You are free to choose any name for this schema.Installing uPLSQL into shared schema is really not recommended as you loose isolation of framework.

If the installing user and utPLSQL owner is one and the same, the user must have the following Oracle system permissions before you can proceed with the installation.

• CREATE SESSION
• CREATE PROCEDURE
• CREATE TYPE
• CREATE TABLE
• CREATE SEQUENCE
• CREATE VIEW
• CREATE SYNONYM
• ALTER SESSION
• CREATE TRIGGER

In addition the user must be granted the execute privilege on DBMS_LOCK and DBMS_CRYPTO packages.

utPLSQL is using DBMS_PROFILER tables for code coverage. The tables required by DBMS_PROFILER will be created in the installation schema unless they already exist.The uninstall process will not drop profiler tables, as they can potentially be shared and reused for profiling PLSQL code.

It is up to DBA to maintain the storage of the profiler tables.

Creating schema for utPLSQL

To create the utPLSQL schema and grant all the required privileges execute script create_utplsql_owner.sql from the source directory with parameters:

• user name - the name of the user that will own of utPLSQL object
• password - the password to be set for that user
• tablespace name - the tablespace name to hold data created during test execution

Example invocation:

Installing utPLSQL

To install the utPLSQL framework into your database, go to source directory, run the install.sql providing the schema_name for utPLSQL as parameter.
Schema must be created prior to calling the install script.You may install utPLSQL from any account that has sufficient privileges to create objects in other users schema.

Example invocation:

Installing DDL trigger

To minimize startup time of utPLSQL framework (especially on a database with large schema) it is recommended to install utPLSQL DDL trigger to enable utPLSQL annotation to be updated at compile-time.

It's recommended to install DDL trigger when connected as SYSDBA user. Trigger is created in utPLSQL schema.If using the owner schema of utPLSQL to install trigger, the owner needs to have ADMINISTER DATABASE TRIGGER and CREATE TRIGGER system privileges.If using different user to install trigger, the user needs to have ADMINISTER DATABASE TRIGGER and CREATE ANY TRIGGER system privileges.

To install DDL trigger go to source directory, run the install_ddl_trigger.sql providing the schema_name for utPLSQL as parameter.

Example invocation:

Note:

Trigger can be installed ant any point in time.

Allowing other users to access the utPLSQL framework

In order to allow other users to access utPLSQL, synonyms must be created and privileges granted.You have two options:

• use grants and synonyms to public, to allow all users to access the framework
• use synonyms and grants for individual users to limit the access to the framework

To grant utPLSQL to public execute script source/create_synonyms_and_grants_for_public.sql and provide schema_name where utPLSQL is installed.

Example invocation:

To grant utPLSQL to an individual user, execute scripts source/create_user_grants.sql and source/create_user_synonyms.sql, provide schema_name where utPLSQL is installed and user_name to grant access for.

Example invocation:

The following tools that support the SQL*Plus commands can be used to run the installation script:

• SQL*Plus

In order to use the Code Coverage functionality of utPLSQL, users executing the tests must have the CREATE privilege on the PLSQL code that the coverage is gathered on.This is a requirement of DBMS_PROFILER package.

In practice, user running tests for PLSQL code that he does not own, needs to have CREATE ANY PROCEDURE/CREATE ANY TRIGGER privileges.Running code coverage on objects that the user does not own will not produce any coverage information without those privileges.

To uninstall run uninstall.sql and provide schema_name where utPLSQL is installed.

Example invocation:

The uninstall script will remove all the objects installed by the install script.Additionally, all the public and private synonyms pointing to the objects in the utPLSQL schema will be removed.

If you have extended any utPLSQL types such as a custom reporter, these will need to be dropped before the uninstall, otherwise the uninstall script might fail.

The uninstall script does not drop the schema.

In order for the uninstall to be successful, you need to use the uninstall script that was provided with the exact utPLSQL version installed on your database.i.e. the uninstall script provided with version 3.0.1 will probably not work if you want to remove version 3.0.0 from your database.

Alternatively you can drop the user that owns utPLSQL and re-create it using headless install.

Currently, the only way to upgrade version of utPLSQL v3.0.0 and above is to remove the previous version and install the new version.

If you are using utPLSQL v2, you can still install utPLSQL v3.The only requirement is that utPLSQL v3 needs to be installed in a different schema than utPLSQL v2.

utPLSQL v3 and utPLSQL v2 do not collide on public synonym names.