Re: Oracle problems.

[Quoted] "Leigh G. Bowden" <LGBowden_at_bowdenfamily.fsnet.co.uk> wrote in message news:9uh0g8$7kk$1_at_newsg1.svr.pol.co.uk...

[Quoted] > We have an AlphaServer 2100 4/200 with two CPU's and 1GB of memory and
> plenty of disks - all shadowed. As far as I am aware it is using Oracle
> 7.1.5 on OpenVMS 6.2 AXP.
>
> It appears to be unreliable. After about three weeks this system will lock
> up and the console will report "Insufficient memory for operation" type
> errors and will allow nobody to logon even at the console. The only way to
> get out of this to power cycle it.
>
> Has anybody experienced this before and got any suggestions for a fix??

I do not know if this will help or not, but it sounds like a problem we had with an old version or Oracle (I don't think it was 7.1.5, but it may have been). Make sure you take a look at what the procedure is doing and run it in test mode (i.e. comment out the actual deassign and double check that the table the .COM wants to deassign is actually no longer in use before you let the job run completely) since I do not know what versions of VMS/Oracle we had had this problem on.

--
A study has shown that sheep can remember faces for up to 2 years, I guess
that means I'm dumber than the average sheep.
CLEANUP_ORACLE_LNM_GARBAGE.COM;18


$!
$!


$ command_procedure = "''f$environment("procedure")'"


$ command_procedure = -


"''command_procedure'" - "''F$PARSE(COMMAND_PROCEDURE,,,"VERSION")'"


$ submit/after="00:00+7-00:00"/keep/noprint 'command_procedure

$ log_file = "''f$parse("sys$login:",".log",command_procedure)'" - ";"


$ purge 'log_file /keep=2


$!


$ set noverify


$!


$ show log/table=lnm$system_directory TNS_* -


/output=sys$scratch:cleanup_oracle_lnm_garbage.dat


$ set noon


$! close xfile
$!


$ SAVE_MESSAGE = F$ENVIRONMENT("MESSAGE")


$ SET MESSAGE/NOFACILITY/NOIDENTIFICATION/NOSEVERITY/NOTEXT


$!


$ open /read xfile sys$scratch:cleanup_oracle_lnm_garbage.dat

$ read xfile first_line

$ read xfile second_line


$ read xfile third_line


$loop:


$ read/error= exit_path xfile next_line

$ log_table = f$elem (2, " ", next_line)

$ pid = log_table - "TNS_"


$ write sys$output "Checking ''pid'"


$!


$ if f$getjpi("%X''pid'","USERNAME") .nes. "" then goto loop


$ write sys$output "Deassigning ''log_table'"


$!


$ deassign /user/table=lnm$system_directory 'log_table

$ goto loop

$ exit_path:


$ SET MESSAGE'SAVE_MESSAGE'


$!


$ close xfile

$ delete/nolog/noconfirm sys$scratch:cleanup_oracle_lnm_garbage.dat;*


$ exit