RE: session_cached_cursosrs SPFILES etc.

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Sat, 26 Jan 2002 08:18:45 -0800
Message-ID: <F001.003FC2AA.20020126080524@fatcity.com>

The spfile, pfile work as advertised, well almost!!!!

Oracle9i will look for 1) pfile= syntax in startup command, 2) spfile in ?/dbs directory, 3)good old initSID.ora in ?/dbs directory.

The known problem with spfile is that, while dynamically adding new parameters, Oracle has forgotten to catch the ones that are mutually exclusive.

So, in 9i, one can have db_cache_size= defined in the spfile (first via init.ora), and then dynamically add db_block_buffers= with scope=spfile (these are mutually exclusive parameters). Oracle will not complain. However, if you shutdown your instance, then you will have a problem starting it again with spfile. Oracle will complain about these mutually exclusive parameters and wont start. Now you have to go back to the init.ora file (by removing/remaning spfile). The lesson is: don't get rid of init.ora file when using spfile. And ALWAYS create init.ora from spfile after making changes to spfile, and back it up! You will need it when least expected.

Coming back to the original question of this thread, dynamic modification to session_cached_cursor is not allowed at system level. You can check v$parameter view columns ISSYS_MODIFIABLE, ISSES_MODIFIABLE. The ORA error can be a bit misleading. You will need to restart the database after change is made in the init.ora file. So, here the other lesson is: one has to go through the init.ora->spfile->init.ora->spfile cycle for getting some of such changes implementated, if one wishes to use SPFILE.

Yes, a DBA is still needed to understand and support this stuff :-D

• Kirti

-----Original Message-----
Sent: Saturday, January 26, 2002 7:00 AM To: Multiple recipients of list ORACLE-L

hm, you just gave me a reason to avoid spfile until they mature the way it works :)

• Marin Dimitrov <marin.dimitrov_at_sirma.bg> wrote:
  >
  > ----- Original Message -----
  >
  > > Modify init<sid>.ora + restart the DB
  > >
  >
  > it was a bit trickier because the instance uses spfile and does not
  > care
  > about init.ora
  >
  > I had to export the spfile to pfile, edit the pfile, then import the
  > new
  > pfile back into spfile and restart
  >
  >
  > thanx,
  >
  > Marin
  >
  > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sat Jan 26 2002 - 10:18:45 CST