Why doesn't AlTER SYSTEM SET EVENTS set the events or tracing immediately
?
Why doesn't AlTER SYSTEM SET EVENTS set the events or tracing immediately?
?
?
? 今天看到Tanel的blog，说明了此问题：
? 我们通过alter system 设置events和parameter是不同的，
对于设置parameter使用alter system的影响如下：
1）对当前的session起作用
2）对新用户登录
数据库的用户起作用
3）对已经登录到数据库的其他session起作用
对于使用alter system 设置events影响如下：
1）对当前session起作用
2）对新登录到系统中的用户起作用，但不对其他的session起作用。
1、对于alter system set parameters验证如下：
eg：
目前系统只有三个session登录：
[sql]
SQL> select saddr,sid,serial#,username from v$session where username is not null;
SADDR SID SERIAL# USERNAME
---------------- ---------- ---------- ------------------------------
000000007CFED5D8 33 103 SYS
000000007CFDE178 38 129 SCOTT
000000007CFD4ED8 41 65 RHYS
SQL>
首先修改log_checkpoints_to_alert参数为true，当前用户为sys；
[sql]
SQL> show parameter log_checkpoint
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
SQL> alter system set log_checkpoints_to_alert=true;
System altered.
SQL>
SQL> show user
USER is "SYS"
SQL>
在rhys账户查看该parameter值：
[sql]
SQL> show user
USER is "RHYS"
SQL> select * from v$mystat where rownum<3;
SID STATISTIC# VALUE
---------- ---------- ----------
41 0 0
41 1 12
SQL> show parameter log_checkpoints
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert boolean TRUE
SQL>
?
?
?
在scott账户查看该parameter值：
?
[sql]
SQL> show user
USER is "SCOTT"
SQL> select * from v$mystat where rownum<3;
SID STATISTIC# VALUE
---------- ---------- ----------
38 0 0
38 1 12
SQL> show parameter log_checkpoints
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert boolean TRUE
SQL>
?
?
新登录一个用户Amy；
?
[sql]
SQL> create user amy identified by rhys default tablespace rhys;
User created.
SQL> grant dba to amy;
Grant succeeded.
SQL> select saddr,sid,serial#,username from v$session where username is not null;
SADDR SID SERIAL# USERNAME
---------------- ---------- ---------- ------------------------------
000000007C8836E0 1 83 AMY
000000007CFED5D8 33 103 SYS
000000007CFDE178 38 137 SCOTT
000000007CFD4ED8 41 65 RHYS
SQL>
SQL> show user
USER is "AMY"
SQL> select * from v$mystat where rownum<3;
SID STATISTIC# VALUE
---------- ---------- ----------
1 0 0
1 1 12
SQL> show parameter log_checkpoints_to_alert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert boolean TRUE
SQL>
?
?
?
2、对于对于alter system set events验证如下：
?
[sql]
SQL> select saddr,sid,serial#,username from v$session where username is not null;
SADDR SID SERIAL# USERNAME
---------------- ---------- ---------- ------------------------------
000000007CFED5D8 33 103 SYS
000000007CFDE178 38 137 SCOTT
000000007CFD4ED8 41 65 RHYS
SQL> show user
USER is "SYS"
SQL> alter system set events '10046 trace name context forever,level 12';
System altered.
SQL>
SQL> select saddr,sid,s.serial#,s.username,spid from v$session s,v$process p where s.paddr=p.addr and s.username is not null;
SADDR SID SERIAL# USERNAME SPID
---------------- ---------- ---------- ------------------------------ --------------