密码即将过期提示的影响简析（SP2-0619、SP2-0611）

密码即将过期提示的影响简析

2012-08-30 09:13:56      我来说两句       作者： chen11-1

 

当用户密码即将过期时，在登录时 Oracle会提示ORA-28002错误，但是并不会影响正常的登录。

 

本来认为这个信息并没有太大的影响，但是没想到这个错误会导致SET AUTOTRACE功能失效：

 

solaris*orcl-/home/oracle$ sqlplus test/test

  www.2cto.com  

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 13 11:27:28 2012

 

Copyright (c) 1982, 2011, Oracle. tb 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  www.2cto.com  

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 |  tb   1   (0)| 00:00:01 |

|   1 | LOAD TABLE CONVENTIONAL | T    |       |       |            |          |

---------------------------------------------------------------------------------

  www.2cto.com  

 

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

  www.2cto.com  

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启用时报错不正确的用户名密码错误的。