topic Re: Oracle 8.1.7.4 on VMS 7.3-2 Lock Problem in Operating System - OpenVMS

Oracle 8.1.7.4 on VMS 7.3-2 Lock Problem

Douglas Fisher — Thu, 24 Jan 2008 20:12:51 GMT

We have a repeating problem which requires the
db to be recycled.

A table becomes locked causing a major backup.
The db says the session causing the lock has gone away. Indeed the lsnr job is gone.

Our assigned dba says under VMS, a lock whose
session is gone cannot be cleared - hence db restart.

Does this ring true?
Is there a way to clear a table lock under this
condition?

Thank you in advance....Doug

Re: Oracle 8.1.7.4 on VMS 7.3-2 Lock Problem

Dale A. Marcy — Thu, 24 Jan 2008 20:30:34 GMT

If you can get into the database as a privileged user, you can try the following:

sql> select sid, serial#, username, status from v$session;
.
.
.

.
.
.
sql> alter system kill session ',';

The and are obtained from the listing finding the match to the username. These are commands our Oracle DB has given me for users that lost their sessions, but they are still in the database. It kills their attachment to the database.

Re: Oracle 8.1.7.4 on VMS 7.3-2 Lock Problem

Douglas Fisher — Thu, 24 Jan 2008 20:40:11 GMT

Thank you very much, I'll diplomatically pass this on to our dba group and keep you updated.

Re: Oracle 8.1.7.4 on VMS 7.3-2 Lock Problem

DSM_1 — Fri, 25 Jan 2008 04:46:59 GMT

As an Oracle DBA with many years experience with VMS, I can state that I have NEVER had to restart the database just to clear a database lock. The operating system should not matter. As far as database locks go, Oracle is Oracle, whether it is running on VMS, Unix or Windows.

It sounds like you are experiencing what is usually called a "blocking lock". One process is holding a lock on a database object which one or more other processes wish to obtain. Identifying the process holding the lock is the trick.

It is not always possible to identify which process is of interest from information in v$session, alone. It depends on the application.

I use a third party product which has made me lazy and out of practise in using SQL to find the problem. But Oracle provide a script in Ora_Rdbms_Admin called UTLLOCKT.SQL which may help. If you have access to an Oracle Enterprise Manager console, it may have tools to show which process is locking which object. Mine does, but it is version 9.2

Usually, if you can identify the Oracle process holding the lock, and kill it, the lock is freed immediately and processing continues. However, if the rogue process has done a massive update, then it may take some time for Oracle to roll back the update. This means the lock can sometimes be held for long after the process has been killed. This is true for all operating systems.

Re: Oracle 8.1.7.4 on VMS 7.3-2 Lock Problem

Wim Van den Wyngaert — Fri, 25 Jan 2008 10:37:55 GMT

Can not test it but if you do a transaction starting from a pc, then power off the pc, the session will stay until the keepalive has timed out or until the pc is rebooted.

Wim

Re: Oracle 8.1.7.4 on VMS 7.3-2 Lock Problem

Wim Van den Wyngaert — Fri, 25 Jan 2008 10:59:25 GMT

And if your version of Oracle is not too old :

You can specify a timeout interval by placing the sqlnet_expire_time parameter in your sqlnet.ora file. For example, setting sqlnet_expire_time=2 will poll the SQL*Net client every two minutes and disconnect the session if there is no activity.

Wim