ORA-02095: specified initialization parameter cannot be modified

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get the following error message when attempting to execute a statement like:

ALTER SYSTEM SET DB_DOMAIN=yocoya.com SCOPE=SPFILE;

ORA-02095: specified initialization parameter cannot be modified

Certain initialization parameters are not modifiable using ALTER SYSTEM, so the only way to change their value is to perform the following steps:

1. CREATE PFILE='path to pfile' FROM SPFILE;
2. save a backup copy of this pfile in case of troubles!
   
3. edit the initialization parameter contained in the pfile;
   
4. restart the db using the newly created pfile instead of the default spfile: STARTUP PFILE='...';
5. CREATE SPFILE FROM PFILE='path to file';
6. restart the db normally, using the default spfile.

How do you if a parameter is modifiable?
If you look at the dynamic view V$PARAMETER, there is a column called ISSYS_MODIFIABLE, if the value of this column is FALSE, then the parameter is not modifiable using ALTER SYSTEM.
You can find a list of modifiable parameters in the Reference Manual (11g, 10gR2, 10gR1).

See message translations for ORA-02095 and search additional resources.

updated June 4: see Graeme's comment.

dbms_utility.get_parameter_value

Don't take me wrong, i know that this topic is no-brainer, but you must understand that the function of this blog is like that of a swiss-knife: i want a unique place where i know i can quickly find what i need, wherever i am.

In this case i am referring to one of the most useful API functions of Oracle, a function that allows you to peek at the value of an initialization parameter even if you are not the DBA.
In fact if you do not hold DBA rights or select rights on dynamic view V$PARAMETER, you won't be able to query that view and here is where dbms_utility.get_parameter_value comes in handy.

Although the link is pointing to the 10R2 documentation set, i'm fairly sure (correct me if i am wrong) that this function underwent no significant changes since version 8i and the PL/SQL sample code included in the Example section is ready-to-use.

For instance, this morning i wanted to check on a remote db where i have no special rights if parameter UTL_FILE_DIR was set or not:

DECLARE
 parnam VARCHAR2(256);
 intval BINARY_INTEGER;
 strval VARCHAR2(256);
 partyp BINARY_INTEGER;
BEGIN
 partyp := dbms_utility.get_parameter_value('utl_file_dir',
                                           intval, strval);
 dbms_output.put('parameter value is: ');

 IF partyp = 1 THEN
  dbms_output.put_line(strval);
 ELSE
  dbms_output.put_line(intval);
 END IF;

 IF partyp = 1 THEN
  dbms_output.put('parameter value length is: ');
  dbms_output.put_line(intval);
 END IF;

 dbms_output.put('parameter type is: ');

 IF partyp = 1 THEN
  dbms_output.put_line('string');
 ELSE
  dbms_output.put_line('integer or boolean');
 END IF;
END;

The name of the parameter can be specified in either lowercase or uppercase.
Should the parameter name be wrong, the following error will be raised:

ORA-20000: get_parameter_value: invalid or unsupported parameter ...

Thanks for your patience, i'll be back shortly with some more intriguing Oracle situations :-)

Updated October 2
As Jacques noted, the parameter name must be supplied in lowercase for Oracle versions prior to 10G.

ORA-02393: exceeded call limit on CPU usage

If you get the following message:

ORA-02393: exceeded call limit on CPU usage

it means that there is a profile attached to the user definition with an enforced limit on the CPU_PER_CALL parameter.

Such restrictions work only if system parameter RESOURCE_LIMIT is set to TRUE, which can also be set dynamically with

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=MEMORY;

but are not enforced for user sessions there were already connected prior to executing the ALTER command.

In other words, if you want to enforce these limits, you'll need first to disconnect all affected user sessions or bounce the database supposing that you made the change permanent by indicating SCOPE=SPFILE or SCOPE=BOTH.

Note also that profile changes incur in the same problem, so if the DBA adjusts your CPU_PER_CALL limit, the change will not affect currently opened sessions.

Unfortunately there is no built-in view that allows a normal user to query his/her profile limits.
Only the DBA or a user with the necessary privileges can query view DBA_PROFILES and the usual alternate views beginning with the prefixes ALL and USER do not exist in this case.

See message translations for ORA-02393 and search additional resources.

---

ORA-02393: superato il limite di chiamate sull'uso della CPU
ORA-02393: ha excedido el límite de llamadas para uso de CPU
ORA-02393: s'ha excedit el límit de crides en l'ús de la CPU
ORA-02393: limite d'appel sur utilisation CPU dépassée
ORA-02393: Aufrufgrenze (Call limit) für CPU-Nutzung überschritten
ORA-02393: ξεπεράσθηκε το όριο κλήσεων για χρήση ΚΜΕ
ORA-02393: overskrider opkaldsgrænsen for CPU-forbrug
ORA-02393: högsta antalet anrop för CPU-bruk har överskridits
ORA-02393: overskred kallgrensen for prosessorbruk
ORA-02393: kutsujen enimmäismäärä keskusyksikössä ylitetty
ORA-02393: CPU hívás szám korlát túllépve
ORA-02393: s-a depăşit limita de apel la folosirea CPU
ORA-02393: Aanroeplimiet voor CPU-gebruik is overschreden.
ORA-02393: limite de chamada excedido no uso de CPU
ORA-02393: excedido limite de chamada na utilização de CPU
ORA-02393: превышен предел вызовов на использование CPU
ORA-02393: překročen limit volání na využití CPU
ORA-02393: prekročený limit spojenia na čas CPU
ORA-02393: przekroczono ograniczenie użycia CPU przez wywołanie
ORA-02393: aramanın CPU kullanımı sınırı aşıldı