Autotrace oddity

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>