Shulou(Shulou.com)06/01 Report--

1. Execute statement tracking under scott user today with the following error:

SCOTT@seiang11g > set autotrace traceonly statistice

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

2. According to the above error message analysis, because the scott user does not have the PLUSTRACE role, use the SYS user to grant the scott user the permission of the PLUSTRACE role:

SYS@seiang11g > grant PLUSTRACE to scott

Grant PLUSTRACE to scott

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

But the error message indicates that the PLUSTRACE role does not exist because the PLUSTRACE role is not automatically created when the database is created. It needs to be created manually by running the plustrce.sql script.

[oracle@seiang11g ~] $cd $ORACLE_HOME/sqlplus/admin

[oracle@seiang11g admin] $ll

Total 16

-rw-r--r-- 1 oracle oinstall 466 Jul 13 13:13 glogin.sql

Drwxr-xr-x 2 oracle oinstall 81 Jul 13 10:01 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

The content of the plustrace.sql script is as follows:

[oracle@seiang11g 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

Execute the script under the SYS user:

SYS@seiang11g > @? / sqlplus/admin/plustrce.sql

SYS@seiang11g >

SYS@seiang11g > drop role plustrace

Drop role plustrace

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

SYS@seiang11g > create role plustrace

Role created.

SYS@seiang11g >

SYS@seiang11g > grant select on v_$sesstat to plustrace

Grant succeeded.

SYS@seiang11g > grant select on v_$statname to plustrace

Grant succeeded.

SYS@seiang11g > grant select on v_$mystat to plustrace

Grant succeeded.

SYS@seiang11g > grant plustrace to dba with admin option

Grant succeeded.

SYS@seiang11g >

SYS@seiang11g > set echo off

Script execution complete!

3. Finally, authorize the PLUSTRACE role to scott users:

SYS@seiang11g > grant PLUSTRACE to scott

Grant succeeded.

SCOTT@seiang11g > set autotrace traceonly statistics

Note: after authorizing the PLUSTRACE role to the scott user, you need to reconnect to the scott user to turn on session tracking.

SCOTT@seiang11g > insert into emp1 select * from emp1

14 rows created.

Statistics

15 recursive calls

22 db block gets

33 consistent gets

5 physical reads

1872 redo size

834 bytes sent via SQL*Net to client

791 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

14 rows processed

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.