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
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
$! 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
$! Extract PID from table name
$! 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
$! Don't know what this table is, output message about it
$ WRITE SYS$OUTPUT "Unrecognised table name ''Table'"
$! 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/USER/TABLE=LNM$SYSTEM_DIRECTORY 'Table'
$ GOTO LOOP
$ CLOSE in
$ DELETE ORACLE_TABLES.TMP;