对于初次查看执行计划的用户需要一个plustrace角色，否则就会报下面的错：
-
SQL> set autotrace on
-
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
-
SP2-0611: Error enabling STATISTICS report
---查看脚本内容
-
[oracle@zh ~]$ cd $ORACLE_HOME/sqlplus/admin/
-
[oracle@zh admin]$ ll
-
total 20
-
-rw-r--r-- 1 oracle oinstall 368 Apr 10 2011 glogin.sql
-
drwxr-xr-x 2 oracle oinstall 4096 Dec 8 17:34 help
-
-rw-r--r-- 1 oracle oinstall 226 Jul 17 2013 libsqlplus.def
-
-rw-r--r-- 1 oracle oinstall 813 Mar 7 2006 plustrce.sql
-
-rw-r--r-- 1 oracle oinstall 2118 Feb 16 2003 pupbld.sql
-
[oracle@zh admin]$ cat 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
- [oracle@zh admin]$
-
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
- SQL>
-
SQL> grant PLUSTRACE to hr;
-
-
Grant succeeded.
-
-
SQL>
-
SQL>
-
SQL> conn hr/hr
-
Connected.
-
SQL> set autotrace on
-
SQL>
- SQL> set autotrace off
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31399171/viewspace-2131415/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
