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)


相关推荐

  • 《开源安全运维平台-OSSIM最佳实践》已经上市

    《开源安全运维平台-OSSIM最佳实践》已经上市经多年潜心研究开源技术,历时三年创作的《开源安全运维平台OSSIM最佳实践》一书即将出版。该书用100多万字记录了作者10多年的OSSIM研究应用成果,重点展示了开源安全管理平台OSSIM在大型企业网运维管理中的实践。国内目前也有各式各样的运维系统,经过笔者对比分析得出这些工具无论在功能上、性能上还是在安全和稳定性易用性上都无法跟OSSIM系统想媲美,而且很多国内的开源安全运维项目在发布几年后就逐步淡出了舞台,而OSSIM持续发展了十多年。

    2022年10月25日
  • CTF-UPX脱壳加壳讲解;(详细版)

    CTF-UPX脱壳加壳讲解;(详细版)在做CTF-RE题的时候,下载的题目附件会发现缺少函数方法的现象,说明这个文件就被加壳处理了;这个是加壳状态下的;脱壳后~~~~~~~如何发现是加壳的呢?除了开头所描述的方法,还有第二种用ExeinfoPE软件查看附件信息;此时这个软件就提示我们这个附件是UPX加壳处理的;二.脱壳这里我只讲一种方法(因为我只会一种方法-.-)首先下载好打包好的UPX脱壳工具,解压下载好:讲一下用法吧在这个文件夹当中输入cmd进入;输入upx.exe-h有如下反应:

  • auto.js微信自动回复脚本_微信群助手机器人

    auto.js微信自动回复脚本_微信群助手机器人一、前言整体思路1)找到头像右上角有消息标志的聊天(注意直接跑下面代码的时候请确保聊天界面由此前提)2)点击进入聊天窗口,找到所有消息3)取最后一个消息(最新消息)4)和之前的新消息对比是否发生变化5)新消息推送至API6)收到API消息发送微信v8版本发送消息时,不再显示“发送”按钮了,也就没办法用找到“发送”控件的方法实现发送消息了。尝试用KeyCode(code)方式,发送回车键,发现也无效,原因查了一下好像是需要ROOT还是安卓9以上此方法失效。于是用坐标点击的方式点击键盘上的

  • UCI数据集整理(附论文常用数据集)

    UCI数据集整理(附论文常用数据集)摘要:UCI数据集作为标准测试数据集经常出现在许多机器学习的论文中,为了更方便使用这些数据集有必要对其进行整理,这里整理了论文中经常出现的数据集,并详细介绍如何使用MATLAB将数据集文件整理成自己需要的格式以及如何使用数据集文件。要点如下UCI数据集介绍用程序整理数据集如何使用数据集文件点击跳转至UCI数据集下载页1.前言UCI数据集是一个常用的机器…

  • 2017 上海车牌(沪牌)标书购买攻略

    2017 上海车牌(沪牌)标书购买攻略本文介绍 上海车牌上海牌照标书购买的攻略,本文会一直跟随政策而更新, 希望对大家有所帮助本文2017年4月更新阅读目录第一步:确认自己是否有资格购买标书一定要确认自己的

  • We Make AI-Ops Happen 杭州云栖大会——智能运维专场即将开启

    We Make AI-Ops Happen 杭州云栖大会——智能运维专场即将开启

发表回复

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

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