Re: Dropping Many Synonyms?

Your comments below explain everything. You are not getting the problem when
SHUTTING the database as you put in
your original post; you are getting it when OPENING the database - it's just that you notice it because you immediately shut the database afterwards.

Not sure about the problem with setospid, VMS is a law unto itself. You my try
oradebug setorapid {process id from v$process}.

however - the problem is probably that you generate a list of synonyms to drop with an SQL statement that does a large sort, and you have a temporary tablespace that allocates this segment in very small extents.

When the database shuts down the FIRST
time, the instance releases the temporary segment (you are using a proper 'contents TEMPORARY' tablespace for TEMP I assume). When the database restarts, SMON starts to clear up the garbage left over from previous temporary segments.

If there are now a few hundred or so extents in the temp tablespace, SMON could be kept very busy for many minutes. My worst case as 14,000 extents which cause it to thrash for about an hour. I have heard of worse.

There is an article about SMON on my
web-site if you want more details; in the meantime, check the initial/next extent sizes on the temporary_tablespace for
the user generating the list of synonyms.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

derman_at_optonline.net wrote in message ...
>Jonathan,
>
>I'm running Oracle 7.3.3.6 on DEC Alpha OpenVMS 7.1
>
>My synonym dropping usually goes like this:
>
>- drop many synonyms
>- shutdown immediate (happens fast)
>- startup restrict (happens fast)
>- shutdown normal (taks a looong time, with lots of SMON activity)
>

>- oradebug setospid 0000E11F (took a minute for prompt to return)
>
>ORA-03113: end-of-file on communication channel
>
>...and inside the trace file, I see:
>
>*** SESSION ID:(8.1) 1999.11.26.09.58.38.052
>ksedmp: internal or fatal error
>ORA-00075: process "pid: e11f, image: " not found in this instance
>
Received on Fri Nov 26 1999 - 12:30:36 CST