role 'PLUSTRACE' does not exist

role 'PLUSTRACE' does not exist

大家好,又见面了,我是全栈君,今天给大家准备了Idea注册码。

I have created a new user named watson and granted the related priviledges as following:

SQL> create user watson identified by watson;

SQL> grant resource ,connect,create session to watson;

 

There will be an error happened when we use this new user to trace the execution plan.The following is the prompt hinted by oracle database.

SQL> grant plustrace to watson;
grant plustrace to watson
      *
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

The reason is that we have not run the related sql statement which supports this function. Below is the scritpt where is coming from.

$ORACLE_HOME/sqlplus/admin/plustrace.sql

We can take a glance at this script to have a understanding of what this function is.

 

[oracle@TEST11G ~]$ vi $ORACLE_HOME/sqlplus/admin/plustrce.sql

— Copyright (c) Oracle Corporation 1995, 2002.  All Rights Reserved.

— NAME
—   plustrce.sql

— DESCRIPTION
—   Creates a role with access to Dynamic Performance Tables
—   for the SQL*Plus SET AUTOTRACE … STATISTICS command.
—   After this script has been run, each user requiring access to
—   the AUTOTRACE feature should be granted the PLUSTRACE role by
—   the DBA.

— USAGE
—   sqlplus “sys/knl_test7 as sysdba” @plustrce

—   Catalog.sql must have been run before this file is run.
—   This file must be run while connected to a DBA schema.

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

 

So the only thing we need to do is to execute this script by sys system priviledge as following:

[oracle@TEST11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 05:48:21 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

SQL> create role plustrace;

Role created.

SQL>
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>
SQL> set echo off

 

Up to this step, we all know that this script has been executed successfully. So we can grant plustrace role priviledge to the user who we will need to trace the sql stament execution plan.

 

SQL> grant plustrace to watson;

Grant succeeded.

 

In order to show the execution plan successfully, we also need to do the one more steps, which is to create the plan_table by a script offered by oracle,if not executed.

[oracle@TEST11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 06:24:28 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

 

Now the common user watson have the function of trace sql execution plan as following:

SQL> set autotrace traceonly
SQL> select * from t1;

Execution Plan
———————————————————-
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     2 |   380 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     2 |   380 |     3   (0)| 00:00:01 |
————————————————————————–

Note
—–
   – dynamic sampling used for this statement (level=2)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1442  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

 

Now the error has been resolved. To conclusion, there are two important scripts we need to know.

1,  $ORACLE_HOME/sqlplus/admin/plustrce.sql

2,  $ORACLE_HOME/rdbms/admin/utlxplan.sql

版权声明:本文博主原创文章。博客,未经同意不得转载。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/117096.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • countdowntimer_TIMESTAMPDIFF

    countdowntimer_TIMESTAMPDIFF需求:加载某一个界面,在页面中待5秒后再关闭效果图如下:设置了一个点击事件,当文字显示为Skipactivity时,点击跳转界面。代码及介绍如下图:核心功能代码如下Android自带的CountDownTimer这个工具类,也是通过Handler和子线程来实现的。//倒计时工具类CountDownTimer//CountDownTimer的构造方法有两个参数…

  • pycharm安装后运行不了_pycharm暂停程序

    pycharm安装后运行不了_pycharm暂停程序原博客链接:http://blog.csdn.net/qingyuanluofeng/article/details/46501427问题:pycharm安装后不能执行python脚本。我的是执行后老是报错,但是之前在cpython中都是可以的。于是上网查询解决方法原因:pycharm没有设置解析器/解释器设置错误(我的就是因为这个之前设置错了,位置也是错的,结果导致程序不能正常运行出

  • 从零使用qemu模拟器搭建arm运行环境

    从零使用qemu模拟器搭建arm运行环境本文从零开始介绍如何搭建qemu+arm的运行环境

  • 数据库课程设计 ——酒店管理系统「建议收藏」

    数据库课程设计 ——酒店管理系统「建议收藏」一、 需求分析1.软件需求(1)酒店管理系统用于满足酒店工作人员和管理人员的需求。(2)酒店管理人员和工作人员可以为酒店房间加入入住和退房记录,并生成相应的报表用于查阅,确认和保存,酒店工作人员可以浏览、查询、统计、添加酒店房间的入住离开信息。管理员可以查询房间信息、查询员工信息、更改房间信息、更改员工信息等。(3)客户可以申请入住酒店,酒店工作人员需要对客户的姓名、性别、身份证号、房间…

  • 2011年1月31日nod32id,nod32免费激活码_NOD32用户名密码

    2011年1月31日nod32id,nod32免费激活码_NOD32用户名密码
    用户名:EAV-40746393密码:f77rk82e7n(适用类型ESS/EAV)

    用户名:EAV-40746397密码:uj7javkt8c(适用类型ESS/EAV)

    用户名:EAV-40899369密码:bsfrudfene(适用类型ESS/EAV)

    用户名:EAV-40898530密码:mpju75r5u7(适用类型ESS/EAV)

    用户名:EAV-40746391密码:aj7urdnr6

  • robotium例子

    robotium例子android基础知识12:android自动化测试04—Robotium:实例(上):http://daimajishu.iteye.com/blog/1556631robotium方法学习实例:http://blog.csdn.net/gzh0222/article/details/7335666Android自动化测试—Robotium:实例(上):http:/

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号