首页 / MYSQL / role&amp;#39;PLUSTRACE&amp;#39;doesnotexist

role&amp;#39;PLUSTRACE&amp;#39;doesnotexist

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了role&amp;#39;PLUSTRACE&amp;#39;doesnotexist，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4765字，纯文字阅读大概需要7分钟。

内容图文

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 ne

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

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

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

内容总结

以上是互联网集市为您收集整理的role&amp;#39;PLUSTRACE&amp;#39;doesnotexist全部内容，希望文章能够帮你解决role&amp;#39;PLUSTRACE&amp;#39;doesnotexist所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

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

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/562302.html

来源：【匿名】

【上一篇】使用导出导入(datapump)方式将普通表切换为分区表【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【role&amp;#39;PLUSTRACE&amp;#39;doesnotexist】教程文章相关的互联网学习教程文章

Mysql5.5 慢查询 trace的配置（转，针对5.5）【图】

1. 慢查询有什么用?它能记录下所有执行超过long_query_time时间的SQL语句, 帮你找到执行慢的SQL, 方便我们对这些SQL进行优化.2. 如何开启慢查询?首先我们先查看MYSQL服务器的慢查询状态是否开启.连接MYSQL然后执行如下命令：（我的已开启所以红圈处显示“ON”,未开启显示“OFF”）开启慢查询非常简单, 操作如下:方法一: 找到mysql的配置文件my.ini(MySQL\MySQL Server 5.5), 在mysqld下方加入慢查询的配置语句(注意:一定要在[mysql...

Linux清理Oracle日志巧用close_trace命令释放误删trace文件【图】

可能很多朋友都遇到过这样的情况，在UNIX/Linux上定期清理Oracle日志文件夹时可能删除到仍被后台进程open着的trace文件，即某些后可能很多朋友都遇到过这样的情况，在UNIX/Linux上定期清理Oracle日志文件夹时可能删除到仍被后台进程open着的trace文件，即某些后台进程一直持有着这些”被已经误删了的“打开文件的描述符(fd)，这种情况下文件系统上该文件实际占用的空间是不会被释放的，，这就造成使用df命令查看文件系统剩余空间和...

OracleEBSSQLTrace日志收集的方法【图】

Oracle EBS SQL Trace日志收集的方法，这个目录实际上是数据库系统参数表(v$parameter)中的user_dump_dest的值，可以执行下边的 Raw Trace的收集方法 1. 打开Trace，Help > Diagnostics > Trace > Trace > Trace with Binds and Waits Trace项代表的意思No Trace – turns trace off.Regular Trace – generates a regular SQL trace by performing the following statement:ALTER SESSION SET SQL_TRACE = TRUE;Trace with Binds...

trace日志

关闭 ORACLE trace 日志功能 alter system set trace_enabled=false select * from v$parameter where NAME like %trace%;关闭 ORACLE trace 日志功能alter system set trace_enabled=falseselect * from v$parameter where NAME like '%trace%';

单表扫描，MySQL索引选择不正确并详细解析OPTIMIZER_TRACE格式

版本, 表大概有815万行 CREATE TABLE t_audit_operate_log ( Fid bigint(16) AUTO_INCREMENT, Fcreate_time int(10) unsigned NOT NULL DEFAULT ‘0‘, Fuser varchar(50) DEFAULT ‘‘, Fip bigint(16) DEFAULT NULL, Foperate_object_id bigint(20) DEFAULT ‘0‘, PRIMARY KEY (Fid), KEY indx_ctime (Fcreate_time), KEY indx_user (Fuser), KEY indx_objid (Foperate_object_id), KEY indx_ip (Fip)) ENGINE=InnoDB...

[MySQL 5.6] 初识5.6的optimizer trace

在MySQL5.6中，支持将执行的SQL的查询计划树记录下来，目前来看，即使对于非常简单的查询，也会打印出冗长的查询计划，看起来似乎不是很可读，不过对于一个经验丰富，对查询计划的生成过程比较了解的DBA而言，这是一个优化SQL的宝藏，因为暴露了大量的内部产生查询计划的信息给用户，这意味着，我们可以对开销较大的部分进行优化。新参数optimizer_trace可以控制是否为执行的SQL生成查询计划树，默认关闭，我们也建议关闭，...

[MySQL5.6] 一个简单的optimizer_trace示例【代码】

[MySQL5.6] 一个简单的optimizer_trace示例前面已经介绍了如何使用和配置MySQL5.6中optimizer_trace（点击博客），本篇我们以一个相对简单的例子来跟踪optimizer_trace的产生过程。本文的目的不是深究查询优化器的实现，只是跟踪optimizer trace在优化器的那一部分输出，因此很多部分只是一带而过，对于需要深究的部分，暂时标注为红色，后续再扩展阅读;之前一直没看过这部分代码，理解起来还是比较困难的… 我们以一...

单表扫描,MySQL索引选择不正确并详细解析OPTIMIZER_TRACE格式

一表结构如下: MySQL 5.5.30 5.6.20 版本, 表大概有815万行 CREATE TABLE t_audit_operate_log ( Fid bigint(16) AUTO_INCREMENT, Fcreate_time int(10) unsigned NOT NULL DEFAULT ‘0‘, Fuser varchar(50) DEFAULT ‘‘, Fip bigint(16) DEFAULT NULL, Foperate_object_id bigint(20) DEFAULT ‘0‘, PRIMARY KEY (Fid), KEY indx_ctime (Fcreate_time), KEY indx_user (Fuser), KEY indx_objid (Foperate_object_...

mysql strace fsync,fdatasync【代码】

8703 attached with 23 threads - interrupt to quit [pid 7089] fsync(9) = 0 [pid 4063] fsync(9) = 0 [pid 4063] fsync(9) = 0 [pid 4061] fsync(4) = 0 [pid 4050] fsync(4) = 0 [pid 4050] fsync(20) = 0 [pid 4061] fsync(4) = 0 [pid 4050] fsync(12) = 0 [pid 4061] fsync(4) = 0 [pid 4050] fsync(14) = 0 [pid 4061] fsync(4) = 0 [pid 4050] fsync(4) = 0 [pid 4061] fsync(4) = 0 [pid 4052] fsync(4) = 0 [pid 4061] ...

利用DTrace实时检测MySQl

与我们大多数人想象的不同，DTrace用于MySQL时不需对MySQL做任何更改。DTrace最强大的“提供器”（provider，是一组可观测的探测器）是FBT（Functional Boundary Tracing，函数边界跟踪）提供器。我们可以把它与PID提供器结合起来，用于深入观测userland过程。只需要对MySQL代码有一些基本了解，我们就可以开发挖掘这些功能了。　　那么，我们如何才能了解MySQL代码呢？有两种方法。第一，你可以买一本好点儿的相关书籍，如MySQL I...

MySQL Query Latency with the DTrace pid Provider

http://dtrace.org/blogs/brendan/2011/03/14/mysql-query-latency-with-the-dtrace-pid-provider/MySQL Query Latency with the DTrace pid Provider标签：blank dtrace rac mysq ref get target ide org 本文系统来源：http://www.cnblogs.com/WCFGROUP/p/6013128.html

MySQL 5.6.20-4 and Oracle Linux DTrace【代码】

https://blogs.oracle.com/wim/entry/mysql_5_6_20_4?utm_source=tuicool&utm_medium=referral By WimCoekaerts-Oracle on Jul 31, 2014 The MySQL team just released MySQL 5.6.20. One of the cool new things for Oracle Linux users is the addition of MySQL DTrace probes. When you use Oracle Linux 6, or 7 with UEKr3 (3.8.x) and the latest DTrace utils/tools, then you can make use of this. MySQL 5.6 is availab...

MySQL5.6的optimizer_trace

MySQL的explain是各种执行计划选择的结果，如果想看整个执行计划以及对于多种索引方案之间是如何选择的？ MySQL5.6中支持这个功能，optimizer_trace 这个是mysql的参数，默认是关闭的1 2 3 4 5 6 7 8 9 10 11 12 13 14mysql> set optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘%optimizer_trace%‘; +------------------------------+----------------------------------...

mysql 执行计划分析三看， explain,profiling,optimizer_trace【代码】

http://blog.csdn.net/xj626852095/article/details/52767963 step 1 使用explain 查看执行计划， 5.6后可以加参数 explain format=json xxx 输出json格式的信息 step 2 使用profiling详细的列出在每一个步骤消耗的时间，前提是先执行一遍语句。 #打开profiling 的设置 SET profiling = 1; SHOW VARIABLES LIKE ‘%profiling%‘;#查看队列的内容 show profiles; #来查看统计信息 show profile block io,cpu for query 3; s...

Mysql5.5 慢查询 trace的配置（转，针对5.5）【图】

log-queries-not-using-indexes #表示记录下没有使用索引的查询 配置好以后重新启动一个MYSQL:我们再观察一下MySQL\MySQL Server 5.5\目录下面是否自动创建了mysql-slow.log文件. 至此就慢查询配置OK了。注：不同版本下的MYSQL慢查询需要配置文件会有所不同，我的是5.5二、不通过配置文件开启慢查询；（这种方法...

首页 / MYSQL / role&amp;amp;#39;PLUSTRACE&amp;amp;#39;doesnotexist

role&amp;amp;#39;PLUSTRACE&amp;amp;#39;doesnotexist

内容导读

内容图文

内容总结

内容备注

内容手机端

【role&amp;amp;#39;PLUSTRACE&amp;amp;#39;doesnotexist】教程文章相关的互联网学习教程文章

TRACE - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程

首页 / MYSQL / role&#39;PLUSTRACE&#39;doesnotexist

role&#39;PLUSTRACE&#39;doesnotexist

【role&#39;PLUSTRACE&#39;doesnotexist】教程文章相关的互联网学习教程文章