Error Generating Autotrace Explain Report
Contents |
9:21 pm UTC Category: Database – Version: 8.1.5 Latest Followup You Asked Hi, Tom How can I get permission to user to use autotrace. I have created
Check Plustrace Role Is Enabled
user and graned him plustrace role, select any table, select any sequence, ora-06512 roles connect and select_any_catalog. After set autotrace on came error: SQL> select count(*) from track; COUNT(*) ---------- 1153129 ERROR: ORA-01031: insufficient privileges SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 2293 consistent gets 0 physical reads 1376 redo size 1078 bytes sent via SQL*Net to client 668 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed I want not to grant dba role to user. Thank you, Oleg and we said... Sounds like you have a common PLAN_TABLE in your database and this user can SELECT from it but not INSERT. For example, I have a plan_table created and a public synonym on it. I then created a user a and did the following: tkyte@TKYTE816> grant connect, resource to a identified by a; Grant succeeded. tkyte@TKYTE816> grant plustrace to a; Grant succeeded. tkyte@TKYTE816> grant select on plan_table to public; Grant succeeded. tkyte@TKYTE816> connect a/a Connected. tkyte@TKYTE816> set autotrace on tkyte@TKYTE816> select count(*) from dual; COUNT(*) ---------- 1 ERROR: ORA-01031: insufficient privileges SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 1 consistent gets 0 physical reads 0 redo size 367 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed tkyte@TKYTE816> connect tkyte/tkyte Connected. tkyte@TKYTE816> grant all on plan_table to a; Grant succeeded. tkyte@TKYTE816> connect a/a Connected. tkyte@TKYTE816> select count(*) from dual; COUNT(*) ---------- 1 tkyte@TKYTE816> set autotrace on tkyte@TKYTE816> select count(*) from dual; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DUAL' Statistics -------------------------
mwidlake in internals, performance. Tags: data dictionary, explain plan, SQL trackback Have you ever tried to use Explain Plan and it gives you an error like the below (this is on 10.2)? DWDBT1> set autotrace on 1 select count(*) 2 from person pers 3 ,person_name pena 4 where pena.pers_id=pers.pers_id 5* and pena.surname='SMITH' DWDBT1> / COUNT(*) ---------- 23586 1 row selected. Execution Plan ---------------------------------------------------------- ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-00959: tablespace 'USER_TEMP' does not exist SP2-0612: Error generating AUTOTRACE EXPLAIN report I seem to run into https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:960830020268 this once a year or so and, as it has happened three times now, this time around my brain had at long last hung onto the details and I remembered the cause. As a clue, I run into this issue when I am using a new development database that was derived from a live one… As you can see from the https://mwidlake.wordpress.com/2010/06/08/i-cant-explain-why/ above error message, tablespace USER_TEMP does not exist. How can you be referencing a tablespace that does not exist? First thing, does the tablespace really not exist and thus the error is real? DWDBT1> @ts_lst Enter value for ts_name: u old 12: where tablespace_name like upper('&ts_name'||'%') new 12: where tablespace_name like upper('u'||'%') Any Key>; TS_NAME INI_EXT_K NEXT_EXT_K MIN_EX --------------------------------------------------- ---------- ---------- ------ MAX_EX PCT MIN_EXTLN ST ---------- --------- --------- --------- UNDOTBS 64 1 2147483645 64 ON USERS 40 40 1 2147483645 0 40 ON 2 rows selected. As you can see, there is no tablespace USER_TEMP. So it must be something to do with PLAN_TABLE, the table that underlies EXPLAIN PLAN. So let's check out that the table exists. DWDBT1> desc plan_table Name Null? Type ----------------------------------------------------------- -------- --------------------- STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER TIMESTAMP DATE REMARKS VARCHAR2(4000) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ... Yes, the table exists. Or a view or something that looks like a table anyway. Let's check further. DWDBT1> @obj_lst Enter value for obj_name: plan_table ol
Role If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to http://www.dbasupport.com/forums/showthread.php?32261-PLUSTRACE-Role 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 error generating to 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 error generating autotrace report 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 get