Oracle Scratchpad

November 17, 2010

Autotrace oddity

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 7:06 pm BST Nov 17,2010

I got an unexpected error message from SQL*Plus a couple of days ago while trying to enable autotrace with statistics:

SQL> set autotrace  traceonly statistics;
ORA-24315: illegal attribute type

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report

This was a little puzzling, but since I get through a lot of different sites, databases, and instances, I did a quick check to make sure that the plustrace role had been created on this system and that the schema I was using to log on had been granted the role. Everything looked okay … except for this error message. So I reconnected (almost as good as rebooting as a method for solving problems) and got the following error message:

SQL> connect XXXXXXXX/XXXXXXXX@XXXXXXXX       -- no names, no pack drill
ORA-28002: the password will expire within 7 days

Problem solved. When you enable autotrace statistics SQL*Plus tries to create a second session through your current process (which is one of the reasons why v$session can end up with more rows than v$process) . By an unfortunate coincidence or timing, my password had reached the expiry warning point while my initial session was logged on, so the recursive session got error message ORA-28002 when it tried to connect, and SQL*Plus reported this back in the only way that seemed appropriate.

Once I’d worked out what the problem was all I did was change my password, end all my active sessions, and then logon again. If you want a demonstration of the effect, here’s a simple demo of a very similar problem (cut-n-paste from an SQL*Plus session connected to an 11gR1 database – using schema test_user, when the password wasn’t “fred”):

SQL> alter user test_user identified by fred;

User altered.

SQL> set autotrace traceonly statistics;
ORA-01017: invalid username/password; logon denied

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report


  1. Hm
    I Wonder Why sqlplus Hides the original exception ora-28002
    It should expose it in the Error stack

    Comment by Solrates — November 18, 2010 @ 8:02 pm BST Nov 18,2010 | Reply

  2. Thanks for this explanation! It helped me!

    Comment by Frank — January 25, 2013 @ 2:11 pm BST Jan 25,2013 | Reply

  3. It helped me. Thanks.

    Comment by Mukesh Agrawal — October 29, 2014 @ 2:50 pm BST Oct 29,2014 | Reply

  4. Thanks for the post. Just hit this today….and for the same reason.

    Comment by Chuck Davis — December 19, 2017 @ 3:23 pm BST Dec 19,2017 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by