Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    7

    Unanswered: SET AUTOTRACE ON errors

    Can anyone help. I am trying to set up autotrace for a new Oracle 9i database but I keep getting the following error:

    SP2-0613: Unable to verify PLAN_TABLE format or existence
    SP2-0611: Error enabling EXPLAIN report

    I have run the following, which from all my research should be correct:

    1. Run the $ORACLE_HOME/sqlplus/admin/plustrce.sql as SYS

    2. Run the $ORACLE_HOME/rdbms/admin/utlxplan.sql as the USER I want to run the autotrace

    3. Granted PLUSTRACE to the USER I want to run the autotrace as.

    I can SET AUTOTRACE ON STATISTICS and SET AUTOTRACE OFF, but all other options for SET AUTOTRACE (i.e. ON, ON EXPLAIN and TRACEONLY) seem to fail with the above error.

    Can anyone help me?

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    this is what metalink says about your problem:

    The SET AUTOTRACE ON fails with the following messages;
    SQL> set autotrace on
    SP2-0613: Unable to verify PLAN_TABLE format or existence
    SP2-0611: Error enabling EXPLAIN report SP2-0618: Cannot find the Session Identifier.
    Check PLUSTRACE role is enabled
    SP2-0611: Error enabling STATISTICS report Cause: -------
    The user needs to have AUTOTRACE privilege to run the AUTOTRACE command and needs to create PLAN_TABLE to be populated before using AUTOTRACE command.
    Fix : ----- Logon the SQL*PLUS as USER SYS with this syntax for 8.1.xx releases ;
    SQL> CONNECT sys/<password>
    Or this syntax for the 9.xx releases;
    SQL> CONNECT sys/<password> AS SYSDBA
    Execute the plustrce.sql script ;
    for UNIX;
    SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
    for WINDOWS;
    SQL> @$ORACLE_HOME\sqlplus\admin\plustrce.sql
    Grant Role PLUSTRACE to User ;
    SQL> GRANT plustrace TO <username>;
    Logon as the user requiring the usage of AUTOTRACE ;
    SQL> CONNECT <username>/<password>
    Execute the utlxplan.sql script ;
    for UNIX;
    SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
    for WINDOWS;
    SQL> @$ORACLE_HOME\rdbms\admin\utlxplan.sql
    Set autotrace option on ;
    SQL> SET AUTOTRACE ON
    References ----------
    [NOTE:1055431.6] ORA-01919 Using AUTOTRACE in SQL*Plus [NOTE:43214.1] AUTOTRACE Option

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Jul 2003
    Posts
    7
    Thanks for the reply, but as stated in my original post this is exactly what I have done, but I am still getting the specified error message, hence the request for further assistance.

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    when u r connected, pls check out the view session_roles for the neeeded role. If it is not there try the set role command.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Jul 2003
    Posts
    7
    Thanks again for your reply. Unfortunately this checks out too. I have the correct role, and even if I explicitly set the role to PLUSTRACE I still get the error. I CAN see the plan_table, but I still get the error (see below):

    SQL> select user from dual;

    USER
    ------------------------------
    EUR_USER

    SQL> select * from session_roles;

    ROLE
    ------------------------------
    PLUSTRACE
    RESOURCE
    CONNECT
    SQLADMIN

    SQL> set role PLUSTRACE

    Role set.

    SQL> set autotrace on
    SP2-0613: Unable to verify PLAN_TABLE format or existence
    SP2-0611: Error enabling EXPLAIN report
    SQL> select * from PLAN_TABLE;

    no rows selected


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    3 consistent gets
    0 physical reads
    0 redo size
    2289 bytes sent via SQL*Net to client
    655 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed

    SQL>

  6. #6
    Join Date
    Oct 2003
    Posts
    5
    [you might want to check whether you have the right version of plan_table created.
    I notice you ran $ORACLE_HOME/rdbms/admin/utlxplan.sql script to create plan_table,
    so make sure you $ORACLE_HOME is set up correctly.

  7. #7
    Join Date
    Jul 2003
    Posts
    7
    Not sure what happened here, but I dropped and recreated the user and all seems well. I must have missed something before, but I can't think what as it was all scripted and I used the same scripts. Anyway its OK now, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •