Session altered.
SQL> select s.username,s.sid,p.spid from v$session s,v$process p
2 where s.paddr = p.addr and s.username = 'SCOTT'; --获取需要跟踪的特定SPID
USERNAME SID SPID
------------------------------ ---------- ------------
SYSTEM 159 3365
SQL>ho cat $ORACLE_BASE/admin/orcl/udump/orcl_ora_3365.trc --查看跟踪文件
--以下内容为执行show parameter sga的实际操作语句,实质上是查询v$parameter视图
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,
DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%sga%')
ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
四、启用实例级别会话跟踪产生的问题
使用下面的语句启用实例级别会话跟踪后
alter system set sql_trace = true;
再将其关闭后，重新启动实例出现下列提示
SQL> startup --启动实例
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 469762048 bytes
Fixed Size 1220048 bytes
Variable Size 180355632 bytes
Database Buffers 285212672 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
[oracle@robinson ~]$ oerr ora 32004 --查看具体的错误信息描述有参数设置不妥
32004, 00000, "obsolete and/or deprecated parameter(s) specified"
// *Cause: One or more obsolete and/or parameters were specified in
// the SPFILE or the PFILE on the server side.
// *Action: See alert log for a list of parameters that are obsolete.
// or deprecated. Remove them from the SPFILE or the server
// side PFILE.
--查看告警日志
SQL> ho cat $ORACLE_BASE/admin/orcl/bdump/alert_orcl.log
Deprecated system parameters with specified values:
sql_trace --描述为sql_trace
End of deprecated system parameter listing
SQL> show parameter sql_trace; --查看该参数已经置为false
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean FALSE
--使用reset将其从参数文件中清除后启动正常
SQL> alter system reset sql_trace scope = spfile sid = '*';
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 469762048 bytes
Fixed Size 1220048 bytes
Variable Size 184549936 bytes
Database Buffers 281018368 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
摘自 一沙弥的世界
|