To change your Oracle 9i release 2 database from a dedicated server to a shared server, you must change the number of dispatchers to at least one. To do this you can enter the following command:
alter system set dispatchers='(PROTOCOL=TCP) (DISPATCHERS=1)' scope=both;
This statement is supposed to change the server for the current instance and when the instance is shut down and restarted. But, there is one small problem. If you run this statement you will receive the following error message:
ORA-02065: illegal option for ALTER SYSTEM
The alter system statement is an apparent bug. This statement works without the "scope=" parameter.
The workaround for the alter system statement is as follows:
- Create a pfile from your current spfile. Execute the following statement as sys user:
create pfile='init2test1.ora' from spfile='SPFILETEST1.ORA';
- Edit the just created pfile 'init2test1.ora' by changing the line:
*.dispatchers='(PROTOCOL=TCP) (DISPATCHERS=0)' to *.dispatchers='(PROTOCOL=TCP) (DISPATCHERS=1)'
- Create an spfile -- spfile2test1.ora, from the just edited pfile -- init2test1.ora as a sys user:
create spfile='SPFILE2TEST1.ORA' from pfile='init2test1.ora';
- Shut down the database.
- Copy the just created spfile -- spfile2test1.ora to the current spfile -- spfiletest1.ora
copy 'spfile2test1.ora' to 'spfiletest1.ora'
- Start up the database.
Ofer H. writes: To change any static parameter enter the database "as sysdba" and type:
alter system set dispatchers='(PROTOCOL=TCP) (DISPATCHERS=1)' scope=spfile;then type:
The database will restart using the new spfile parameters.