admin 管理员组文章数量: 887032
set
问题:测试环境上非SYS用户想使用set_sql_trace_in_session跟踪会话信息,但遇到了PLS-00201错误。
解决方法:用sys用户登录授予用户有execute DBMS_SYSTEM的权限
grant execute on DBMS_SYSTEM to &username;
实验过程:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as DEV
--.1从v$session中获取要跟踪的sid,serial#
SQL> SELECT sid ,serial# FROM v$session WHERE osuser='Liangwei';
SID SERIAL#
---------- ----------
331 32948
419 33910
--2.非sys用户,调用dbms_system.set_sql_trace_in_session,出错
SQL> EXEC dbms_system.set_sql_trace_in_session(sid =>'331' ,serial# =>'32948' ,sql_trace => TRUE);
begin dbms_system.set_sql_trace_in_session(sid =>'331' ,serial# =>'32948' ,sql_trace => TRUE); end;
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--3.使用SYS用户授予dev用户execute DBMS_SYSTEM的权限
SQL> show user;
User is "SYS"
SQL> grant execute on DBMS_SYSTEM to dev;
Grant succeeded
SQL> show user;
User is "dev"
SQL> EXEC dbms_system.set_sql_trace_in_session(sid =>'331' ,serial# =>'32948' ,sql_trace => TRUE);
begin dbms_system.set_sql_trace_in_session(sid =>'331' ,serial# =>'32948' ,sql_trace => TRUE); end;
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--4. 一定要加上sys
a)开启会话跟踪
SQL> EXEC sys.dbms_system.set_sql_trace_in_session(sid =>'331' ,serial# =>'32948' ,sql_trace => TRUE);
PL/SQL procedure successfully completed
。。。。等待331号sid执行SQL
b)结束会话跟踪
SQL> EXEC sys.dbms_system.set_sql_trace_in_session(sid =>'331' ,serial# =>'32948' ,sql_trace => FALSE);
PL/SQL procedure successfully completed
--5.udump目录下查看生成的dump文件
/oracle/app/admin/devdb/udump$ls -lrt
total 88
-rw-rw-rw- 1 oracle dba 0 Jul 16 2009 sbtio.log
-rw-r--r-- 1 oracle dba 40741 Jun 03 11:10 13.txt
-rw-r----- 1 oracle dba 3355 Jun 20 17:31 devdb_ora_8659146.trc
本文标签: set
版权声明:本文标题:set 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.freenas.com.cn/jishu/1686661395h21754.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论