当用户密码即将过期时，在登录时Oracle会提示ORA-28002错误，但是并不会影响正常的登录。
本来认为这个信息并没有太大的影响，但是没想到这个错误会导致SET AUTOTRACE功能失效：
solaris*orcl-/home/oracle$ sqlplus test/test SQL*Plus: Release 11.2.0.3.0 Production ON Fri Jul 13 11:27:28 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. ERROR: ORA-28002: the password will expire WITHIN 1 days Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, Oracle Label Security AND REAL Application Testing options SQL> SET autot ON ERROR: ORA-28002: the password will expire WITHIN 1 days SP2-0619: Error while connecting SP2-0611: Error enabling STATISTICS report SQL> ALTER USER test IDENTIFIED BY test; USER altered. SQL> SET autot ON ERROR: ORA-24315: illegal attribute TYPE SP2-0619: Error while connecting SP2-0611: Error enabling STATISTICS report SQL> exit Disconnected FROM Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, Oracle Label Security AND REAL Application Testing options solaris*orcl-/home/oracle$ sqlplus test/test SQL*Plus: Release 11.2.0.3.0 Production ON Fri Jul 13 11:27:52 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, Oracle Label Security AND REAL Application Testing options SQL> SET autot ON SQL> INSERT INTO t VALUES (1, 'a'); 1 ROW created. Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | T | | | | | --------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 71 recursive calls 9 db block gets 75 consistent gets 10 physical reads 1284 redo SIZE 829 bytes sent via SQL*Net TO client 785 bytes received via SQL*Net FROM client 3 SQL*Net roundtrips TO/FROM client 9 sorts (memory) 0 sorts (disk) 1 ROWS processed |
很显然，由于ORA-28002错误导致了SET AUTOTRACE ON功能启用时碰到了错误。当修改了当前的用户密码，则ORA-28002不再出现后，SET AUTOTRACE ON的功能恢复正常。
根据上面的信息其实可以判断，在启用SET AUTOTRACE ON功能时，sqlplus会自动创建一个新的会话来记录当前会话的统计信息。
而启用的新的会话会使用当前会话登录时保存的密码来进行登录，因此可以通过下面的例子来验证这个推论：
solaris*orcl-/home/oracle$ sqlplus test/test SQL*Plus: Release 11.2.0.3.0 Production ON Sun Jul 15 01:28:38 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, Oracle Label Security AND REAL Application Testing options SQL> SET autot ON SQL> SET autot off SQL> ALTER USER test IDENTIFIED BY test1; USER altered. SQL> SET autot ON ERROR: ORA-01017: invalid username/password; logon denied SP2-0619: Error while connecting SP2-0611: Error enabling STATISTICS report |
果然，在用户登录后，如果当前的密码被修改，是会导致SET AUTOTRACE ON启用时报错不正确的用户名密码错误的。