密碼即將過期提示的影響

當使用者密碼即將過期時，在登入時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啟用時報錯不正確的使用者名稱密碼錯誤的。