Setting Events in the SPFILE
Oracle DBAs often have to set events to trace something in the database. For example:
Event='10015 trace name context forever, level 1';
For text-based client parameter files, this can be done easily by editing the file. For databases started using SPFILE, the ALTER SYSTEM command can be used as follows:
SQL>alter system 2>set event='10015 trace name context forever, level 1' scope=SPFILE;
Events can be set in the SPFILE even when the database instance is in the NOMOUNT state.
All events can be removed from the SPFILE using the following SQL:
SQL>alter system reset event scope=SPFILE sid='*';
For the events to take effect, the instance needs to be restarted. There are several restrictions that relate to setting events in the SPFILE:
The instance must have been started using the SPFILE.
The event cannot be set during the life of the instance. This essentially means that the SCOPE clause cannot be set to MEMORY or BOTH.
It's frequently necessary to set events for the sake of dumping a trace of memory or process. In such situations, restarting the database instance is not feasible. The ALTER SESSION command (in contrast to the ALTER SYSTEM command) can be used to achieve this immediate effect. For example:
SQL>alter session set events='immediate trace name controlf level 1';
In the command above, note the use of events (instead of 'event') and the absence of a comma (,) before the word level.