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>