How to change OPTIMIZER_FEATURES_ENABLE in Oracle 9i

oracleoracle9i

I want to change the OPTIMIZER_FEATURES_ENABLE parameter in a local installation of Oracle 9i (32bit 9.2.0.1.0 Enterprise Edition running on Windows Server 2003) in an attempt to reproduce a performance issue reported by a client but I can't figure out how to do it. Both ALTER SESSION and ALTER SYSTEM statements trigger this error (even if run as SYS):

ORA-02095: specified initialization
parameter cannot be modified

How can I change it?

Update:

The steps I followed (thanks to Gary's answer) were this:

  1. Create an empty file: C:\oracle\ora92\DBS\INIT.ORA

  2. Edit the file to add: optimizer_features_enabled = 8.1.7

  3. Launch Windows service manager

  4. Restart the service called OracleServiceFOO, where FOO is my instance name

Best Answer

It's a static parameter so it needs to be changed in the pfile / spfile and then the instance restarted.

Related Topic