Autotrace Error Enabling Statistics Report
Contents |
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn
Sp2-0618: Cannot Find The Session Identifier
JavaScript back on and reload this page. Please enter a sp2-0612: error generating autotrace explain report title. You can not post a blank message. Please type your message and try again.
Ora-01919: Role 'plustrace' Does Not Exist
More discussions in General Database Discussions All PlacesDatabaseGeneral Database Discussions This discussion is archived 11 Replies Latest reply on Jan 17, 2012 12:43 PM by 890169 what is plustrace role in oracle Question about AUTOTRACE, SP2-0618 and SP2-0611 783956 Aug 8, 2010 6:04 AM Good morning, I wanted to look at the execution plan(s) for some sample queries. I did a little research using Google, found out about autotrace, plan_table, plustrace role and utlxplan. I followed the instructions I found but the result isn't sp2-0611 error enabling statistics report 11g quite as expected. Here is an output of a freshly started session: 0 01:50 [SS1TB Attic-Z] [5G] [750M] Z:\Documentation\Oracle\ Oracle Concepts\Scripts>sqlplus hr/abc123 SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 8 01:50:16 2010 Copyright (c) 1982, 2010, 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> select count(*) from plan_table; COUNT(*) ---------- 2 SQL> set autotrace on SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report SQL> select count(*) from plan_table; COUNT(*) ---------- 2 Execution Plan ---------------------------------------------------------- Plan hash value: 3662021055 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCE
Role If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to
Set Autotrace On
register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 6 of 6 Thread: PLUSTRACE Role Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to https://community.oracle.com/thread/1113504 Threaded Mode 12-12-2002,12:39 AM #1 pras View Profile View Forum Posts Member Join Date Jul 2000 Location india Posts 213 PLUSTRACE Role Hi everybody, How do i enable the plustrace role?? when i write SET AUTOTRACE ON I get the foloo error.. SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report http://www.dbasupport.com/forums/showthread.php?32261-PLUSTRACE-Role what should be the problrm.. thanks pras Reply With Quote 12-12-2002,02:03 AM #2 grjohnson View Profile View Forum Posts Senior Advisor Join Date Apr 2001 Location Brisbane, Queensland, Australia Posts 1,203 GRANT PLUSTRACE TO user; OCP 8i, 9i DBA Brisbane Australia Reply With Quote 12-12-2002,03:53 AM #3 pras View Profile View Forum Posts Member Join Date Jul 2000 Location india Posts 213 Hi while granting the role plustrace to the user it gives me the error role plustrace does not exist... SQL> grant plustrace to scott; grant plustrace to scott * ERROR at line 1: ORA-01919: role 'PLUSTRACE' does not exist thanks pras Reply With Quote 12-12-2002,04:09 AM #4 Sameer View Profile View Forum Posts Advisor Join Date Sep 2001 Location Düsseldorf, Germany. Posts 588 Have you execute $ORACLE_HOME/sqlplus/admin/plustrce.sql file ?? This file must be run while logged-in as SYS. Sameer Reply With Quote 12-17-2002,06:28 AM #5 pras View Profile View Forum Posts Member Join Date Jul 2000 Location india Posts 213 Hi After executing the plustrce.sql in sys. I am getting the er
Release 9.2.0.1.0 - ProductionPL/SQL http://myorastuff.blogspot.com/2008/06/how-do-we-enable-autotrace-on-oracle.html Release 9.2.0.1.0 - ProductionCORE 9.2.0.1.0 ProductionTNS for 32-bit Windows: Version 9.2.0.1.0 - ProductionNLSRTL Version 9.2.0.1.0 - ProductionIf we don't set the autotrace, then http://www.programering.com/a/MDMxczMwATg.html we get the below error.SQL> connect scott/tiger@dba1Connected.SQL> set autotrace onSP2-0613: Unable to verify PLAN_TABLE format or existenceSP2-0611: Error enabling EXPLAIN reportSP2-0618: Cannot find error enabling the Session Identifier. Check PLUSTRACE role is enabledSP2-0611: Error enabling STATISTICS reportSQL>Here are the steps to resolve the error.Step 1 Run the plustrace.sql file on sys schema.In windows, the plustrace file is residing at @$ORACLE_HOME\sqlplus\admin\plustrce.sqlIn UNIX, the plustrace file is residing at @$ORACLE_HOME/sqlplus/admin/plustrce.sqlSQL> start d:/oracle/ora92/sqlplus/admin/plustrce.sqlSQL>SQL> drop role error enabling statistics plustrace;drop role plustrace*ERROR at line 1:ORA-01919: role 'PLUSTRACE' does not existSQL> 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_$session to plustrace;Grant succeeded.SQL> grant plustrace to dba with admin option;Grant succeeded.SQL>SQL> set echo offSQL>Step 2Grant the plustrace role to Scott schema.SQL> connect sys/password@dba1 as sysdbaConnected.SQL> grant plustrace to scott;Grant succeeded.SQL>Step 3Create the plan_table on sys schema and grant select privileges to Scott schema.SQL> connect sys/password@dba1 as sysdbaConnected.SQL> CREATE TABLE PLAN_TABLE (2 statement_id varchar2(30),3 plan_id number,4 timestamp date,5 remarks varchar2(4000),6 operation varchar2(30),7 options varchar2(255),8 object_node varchar2(128),9 object_owner varchar2(30),10 object_name varchar2(30),11 object_alias varchar2(65),12 object_instance numeric,13 object_type varchar2(30),14 optimizer varchar2(255),15 search_columns number,16 id numeric,17 parent_id numeric,18 depth numeric,19 position numeric,20 cost numeric,21 cardinality numeric,22 bytes numeric,23 other_tag varchar2(255),24 partition_start varchar2(255),25 partition_stop varchar2(255),26 partition_id numeric,27 other long,28 distrib
statistics Error ORA-942 while gathering statistics SP2-0611: Error enabling STATISTICS report SQL> SQL> SQL> set autotrace Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] SQL> set autotrace trace Error ORA-942 while gathering statistics SP2-0611: Error enabling STATISTICS report SQL> conn sys/root as sysdba Connected. SQL> SQL> SQL> !oerr ora 942 00942, 00000, "table or view does not exist" // *Cause: // *Action: SQL> grant select on v_$sesstat to scott; g Grant succeeded. SQL> rant select on v_$statname to scott; Grant succeeded. SQL> grant select on v_$mystat to scott; Grant succeeded. SQL> SQL> SQL> conn scott/root Connected. SQL> SQL> SQL> set autotrace trace SQL> Posted by Hogan at June 05, 2014 - 9:18 AM ©2016 Programering