alter system set event和set events的区别

今天按照Oracle Support的建议设置event的时候发现set event和set events这两种写法效果不一样，防止忘记先记下来。

1. alter system set event ：此方法对spfile生效，无法对memory生效

SQL> ALTER system SET event=‘1461 trace name errorstack level 3’;
ALTER system SET event=‘1461 trace name errorstack level 3’
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> ALTER system SET event=‘1461 trace name errorstack level 3’ scope=spfile;
System altered.
验证是否已成功更改：

SQL> CREATE pfile=’/home/oracle/1.ora’ FROM spfile;
File created.
SQL> !
[oracle@rac01 ~]$ grep event 1.ora
*.event=‘1461 trace name errorstack level 3’
这种方法的话，需要重启DB。下面Rollback这个参数更改

SQL> ALTER system SET event=‘1461 trace name errorstack off’ scope=spfile;
System altered.
SQL> CREATE pfile=’/home/oracle/2.ora’ FROM spfile;
File created.
SQL> !
[oracle@rac01 ~]$ grep event 2.ora
*.event=‘1461 trace name errorstack off’
2. alter system set events：此方法对memory生效，无法对spfile生效

SQL> ALTER system SET events ‘1461 trace name errorstack level 3’;

System altered.
SQL> !
验证：

[oracle@rac01 ~]$ tail -n1 /oracle/admin/strm1/bdump/alert_strm1.log
OS Pid: 9070 executed alter system set events ‘1461 trace name errorstack level 3’
这个参数更改没有反应到spfile中

SQL> CREATE pfile=’/home/oracle/3.ora’ FROM spfile;
File created.
SQL> !
[oracle@rac01 ~]$ grep event 3.ora
*.event=‘1461 trace name errorstack off’
其实这里的1461是个BUG 6085625，Metalink上可以参照NOTE: 461911.1

另外需要注意的是，RAC环境中用方法2动态设置event的话需要在所有instance设置。不过理解了上面的区别的话这个也就很容易明白了。

注意：设置1461 event无法对已经存在的进程生效，对于设置之后新建立的进程才会有效果。所以，不得不，我们还是得重启DB，让其对所有进程生效。

alter system set events可以在pdb level设置

可以在不同的pdb设置不同的event，
在cdb level设置的参数则会在所有的pdb里都设置

alter system set events是设置在instance里，所有rac环境下需要所有的instance都设置一遍

21:59:52 SQL> show con_name;

CON_NAME
------------------------------
VAULT1PDB88888
21:59:59 SQL> show user;
USER is "SYS"
22:00:02 SQL> show parameter instance_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 vault11
22:00:12 SQL>  alter system set events '10466 trace name errorstack';

System altered.

Elapsed: 00:00:00.05
22:03:13 SQL> SET SERVEROUTPUT ON
DECLARE
    l_level NUMBER;
BEGIN
    FOR l_event IN 10000..47401
    LOOP
        dbms_system.read_ev (l_event,l_level);
        IF l_level > 0 THEN
            dbms_output.put_line ('Event '||TO_CHAR (l_event)||
            ' is set at level '||TO_CHAR (l_level));
        END IF;
    END LOOP;
END;
/22:04:01 SQL> 22:04:01   2  22:04:01   3  22:04:01   4  22:04:01   5  22:04:01   6  22:04:01   7  22:04:01   8  22:04:01   9  22:04:01  10  22:04:01  11  22:04:01  12  22:04:01  13
Event 10466 is set at level 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56

22:03:20 SQL> show con_name;

CON_NAME
------------------------------
VAULT1PDB10001
22:03:23 SQL> show user;
USER is "SYS"
22:03:25 SQL> show parameter instance_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 vault11
22:03:30 SQL>  alter system set events '47400 trace name errorstack';

System altered.

Elapsed: 00:00:00.10
22:03:43 SQL> SET SERVEROUTPUT ON
DECLARE
    l_level NUMBER;
BEGIN
    FOR l_event IN 10000..47401
    LOOP
        dbms_system.read_ev (l_event,l_level);
        IF l_level > 0 THEN
            dbms_output.put_line ('Event '||TO_CHAR (l_event)||
            ' is set at level '||TO_CHAR (l_level));
        END IF;
    END LOOP;
END;
/22:03:54 SQL> 22:03:54   2  22:03:54   3  22:03:54   4  22:03:54   5  22:03:54   6  22:03:54   7  22:03:54   8  22:03:54   9  22:03:54  10  22:03:54  11  22:03:54  12  22:03:54  13
Event 47400 is set at level 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21

23:28:46 SQL> alter system set events '10388 trace name errorstack';

System altered.

Elapsed: 00:00:00.12
23:28:50 SQL> SET SERVEROUTPUT ON
DECLARE
    l_level NUMBER;
BEGIN
    FOR l_event IN 10000..47401
    LOOP
        dbms_system.read_ev (l_event,l_level);
        IF l_level > 0 THEN
            dbms_output.put_line ('Event '||TO_CHAR (l_event)||
            ' is set at level '||TO_CHAR (l_level));
        END IF;
    END LOOP;
END;
/23:29:03 SQL> 23:29:03   2  23:29:03   3  23:29:03   4  23:29:03   5  23:29:03   6  23:29:03   7  23:29:03   8  23:29:03   9  23:29:03  10  23:29:03  11  23:29:03  12  23:29:03  13
Event 10388 is set at level 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
23:29:04 SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
23:32:16 SQL> show user;
USER is "SYS"
23:32:23 SQL> show parameter instance_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 vault11



23:29:44 SQL> SET SERVEROUTPUT ON
DECLARE
    l_level NUMBER;
BEGIN
    FOR l_event IN 10000..47401
    LOOP
        dbms_system.read_ev (l_event,l_level);
        IF l_level > 0 THEN
            dbms_output.put_line ('Event '||TO_CHAR (l_event)||
            ' is set at level '||TO_CHAR (l_level));
        END IF;
    END LOOP;
END;
/23:29:50 SQL> 23:29:50   2  23:29:50   3  23:29:50   4  23:29:50   5  23:29:50   6  23:29:50   7  23:29:50   8  23:29:50   9  23:29:50  10  23:29:50  11  23:29:50  12  23:29:50  13
Event 10388 is set at level 1
Event 47400 is set at level 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
23:29:51 SQL> show user;
USER is "SYS"
23:33:24 SQL> show con_name;

CON_NAME
------------------------------
VAULT1PDB10001
23:33:34 SQL> show parameter instance_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 vault11
23:33:44 SQL>