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;
ERROR:
ORA-24315: illegal attribute type

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

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
ERROR:
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;
ERROR:
ORA-01017: invalid username/password; logon denied

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

2 Comments »

  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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers