因为要看执行计划,所以,就拿hr做实验了
没想到,当我执行
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>
竟然会有这样的报错。
(其实,是可以看到执行计划的,但不能看到statistics report,这个,我倒不知道为什么这个statistics reports 和 plustrace有什么关系。求解释!!!)
根据,SP2-0611
我这样做了:
SQL> conn /as sysdba
Connected.
SQL> show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
其实,我的statistics_level是typical ,是开启这统计的。(10g,默认都是TYPICAL)
故,根据错误SP2-0618提示,知道了,是没有PLUSTRACE这个角色。
SQL> desc dba_roles
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED VARCHAR2(8)
SQL> select * from dba_roles where role='PLUSTRACE';
no rows selected
也确实没有。
这样,我们就这样 新建一个角色好了。。。
SQL> show user
USER is "SYSTEM"
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
grant select on v_$sesstat to plustrace
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn /as sysdba
Connected.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL> grant plustrace to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> set autotrace on
SQL> show user
USER is "HR"
SQL>
这样,我们的执行计划就可以执行了。
(其实,是可以看到执行计划的,但不能看到statistics report,这个,我倒不知道为什么这个statistics reports 和 plustrace有什么关系。求解释!!!)
参照:
https://forums.oracle.com/forums/thread.jspa?threadID=1111504
没想到,当我执行
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>
竟然会有这样的报错。
(其实,是可以看到执行计划的,但不能看到statistics report,这个,我倒不知道为什么这个statistics reports 和 plustrace有什么关系。求解释!!!)
根据,SP2-0611
我这样做了:
SQL> conn /as sysdba
Connected.
SQL> show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
其实,我的statistics_level是typical ,是开启这统计的。(10g,默认都是TYPICAL)
故,根据错误SP2-0618提示,知道了,是没有PLUSTRACE这个角色。
SQL> desc dba_roles
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED VARCHAR2(8)
SQL> select * from dba_roles where role='PLUSTRACE';
no rows selected
也确实没有。
这样,我们就这样 新建一个角色好了。。。
SQL> show user
USER is "SYSTEM"
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
grant select on v_$sesstat to plustrace
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn /as sysdba
Connected.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL> grant plustrace to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> set autotrace on
SQL> show user
USER is "HR"
SQL>
这样,我们的执行计划就可以执行了。
(其实,是可以看到执行计划的,但不能看到statistics report,这个,我倒不知道为什么这个statistics reports 和 plustrace有什么关系。求解释!!!)
参照:
https://forums.oracle.com/forums/thread.jspa?threadID=1111504