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
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 ---------------- ---------- ---------- ------------------------------ ------------------------ 000000007CFDE178 38 137 SCOTT 2176 000000007CFED5D8 33 103 SYS 1981 000000007CFD4ED8 41 65 RHYS 2092 SQL> SQL> ! [oracle@oracle-one ~]$ cd /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/ [oracle@oracle-one trace]$ ls -l *1981* -rw-r-----. 1 oracle oinstall 23897 Oct 9 09:58 RHYS_ora_1981.trc -rw-r-----. 1 oracle oinstall 390 Oct 9 09:58 RHYS_ora_1981.trm [oracle@oracle-one trace]$
可以看到sys用户已经存在trace文件了。
然后在rhys用户下查看一下信息：
[sql]
SQL> select * from v$mystat where rownum<3;
SID STATISTIC# VALUE
---------- ---------- ----------
41 0 0
41 1 19
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
BONUS
AMY
A
B
C
T
SYS_TEMP_FBT
SALGRADE
EMP
10 rows selected.
SQL> !
[oracle@oracle-one ~]$ cd /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/
[oracle@oracle-one trace]$ ls -ltr *2092*
-rw-r-----. 1 oracle oinstall 1211 Oct 9 10:02 RHYS_ora_2092.trm
-rw-r-----. 1 oracle oinstall 164508 Oct 9 10:02 RHYS_ora_2092.trc
[oracle@oracle-one trace]$
同样也存在trace文件了。
那么对于新增的用户呢？我们使用Amy账户登录。
[sql]
SQL> show user
USER is "AMY"
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
---------------- ---------- ---------- ------------------------------ ------------------------
000000007CFDE178 38 137 SCOTT 2176
000000007CFED5D8 33 103 SYS 1981
000000007CFD4ED8 41 65 RHYS 2092
000000007CFC2998 47 137 AMY 5500
SQL> select * from v$mystat where rownum<3;
SID STATISTIC# VALUE
---------- ---------- ----------
47 0 0
47 1 18
SQL> !
[oracle@oracle-one ~]$ cd /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/
[oracle@oracle-one trace]$ ls -l *5500*
-rw-r-----. 1 oracle oinstall 184626 Oct 9 10:25 RHYS_ora_5500.trc
-rw-r-----. 1 oracle oinstall 1095 Oct 9 10:25 RHYS_ora_5500.trm
[oracle@oracle-one trace]$
