  Oracle and INSFMEM Errors? 
 The Question is:
 
Is it possible to prevent PAGEDYN becoming to fragmented during Oracle
 processing. LNM-blocks in PAGEDYN growing to more than 70% and would never be
 released again.
 
 The Answer is :
 
  The OpenVMS Wizard suspects you might be accumulating logical name
  tables -- please contact Oracle for a simple fix for this (known)
  leak in Oracle.  Please also contact Oracle for any issues that
  might be specific to the use and the operation of Oracle software.
 
  Here is a command procedure that can clean up the stale logical name
  tables that can accumulate:
 
 
$!
$! Command procedure to clean up logical name tables left by Oracle
$! processes in order to recover lost PAGEDYN
$!
$! Reported against Oracle V7.1.4
$!
$! When an Oracle process is created, a logical name table is also created
$! to pass information to the new process. Since this is a permanent,
$! shareable table, it is not deleted when the process terminates. Over
$! time, the accumulation of logical names will deplete PAGEDYN to the point
$! that the system hangs or crashes.
$!
$! The logical name tables have names of the form TNS_xxxxxxxx where xxxxxxxx
$! is the PID of the process for which it was created. It is therefore quite
$! straightforward to detect "orphaned" tables by testing to see if the
$! owner process still exists.
$!
$! WARNING - The SHOW LOGICAL/STRUCTURE command is used to generate a list
$!  of all shareable logical name tables on the system. Since the command
$!  takes out the LNM spinlock for the duration of the command, it will
$!  hang most user processes for its duration. If there is a large accumulation
$!  of logical name tables, this can be a significant time. One report was
$!  for a hang of 20mins when displaying 10MB of logical names.
$!
$!  If this procedure is executed frequently, the duration of the hang should
$!  be minimal. Note that the size/response time of the command is non-linear.
$!
$!  Requires SYSPRV and/or BYPASS privilege
$!
$!   Author: John Gillings
$!           Systems Software Consultant
$!           Compaq Customer Support Centre
$!           Sydney, Australia
$!
$!   email:  John.Gillings@Compaq.com
$!
$! PROCEDURE
$!
$! First list all shareable logical name tables
$!
$ SHOW LOGICAL/STRUCTURE/OUT=LNMLIST.TMP
$!
$! Filter for Oracle logical name tables
$!
$ SEARCH LNMLIST.TMP "(TNS_" /OUT=ORACLE_TABLES.TMP
$ DELETEXX LNMLIST.TMP;
$ OPEN/READ in ORACLE_TABLES.TMP
$!
$! Scan Oracle tables
$!
$ loop: READ/END=Finished/ERROR=Finished in Table
$!
$!    Get the table name by removing all whitespace and brackets
$!
$   Table=F$EDIT(Table,"COLLAPSE")-"("-")"
$!
$!    Extract PID from table name
$!
$   pid=F$ELEMENT(1,"_",Table)
$!
$!    Ignore if process still exists. If process does not exist, the
$!    F$GETJPI call will fail with %SYSTEM-W-NONEXPR, nonexistent process
$!    and thus fall through. We will also fall through if there is any
$!    mismatch between the input and output PIDs. This should never
$!    happen, but it's safer code.
$!
$   ON WARNING THEN CONTINUE
$   IF F$GETJPI(pid,"PID").EQS.pid THEN GOTO loop
$!
$!    Sanity check PID format, make sure it's a valid, non-zero, 8 digit
$!    hex string. F$INTEGER will return 0 if there are any non-numeric
$!    characters in the input string
$!
$   IF F$INTEGER("%X''pid'").EQ.0.OR.F$LENGTH(pid).NE.8
$   THEN
$!
$!    Don't know what this table is, output message about it
$!
$     WRITE SYS$OUTPUT "Unrecognised table name ''Table'"
$   ELSE
$!
$!    Passed all tests, this must be an orphaned logical name table.
$!
$!    Delete the logical names in table and the table itself.
$!    Note that the DEASSIGN/ALL should be redundant, but again, it's
$!    safer to be explicit
$!
$     WRITE SYS$OUTPUT "Deleting table ''Table'"
$     DEASSIGN/ALL/TABLE='Table'
$     DEASSIGN/USER/TABLE=LNM$SYSTEM_DIRECTORY 'Table'
$   ENDIF
$ GOTO LOOP
$ Finished:
$ CLOSE in
$ DELETE ORACLE_TABLES.TMP;
$ EXIT
 
 Answer written or last revised on  29-MAR-2002 
