Till I read this oracle-l thread, I was thinking pasword file is only updated/touched when sys user password is changed however, it is not the only case. As you can see below it is updated/touched whenever a user password is changed, interestingly password file is not updated/touched for new user creations. My question is why oracle updates/touches the file for regular user passwords and why it doesn’t updates/touches for user creation ? If anybody knows can you please leave a comment.
SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
Volume in drive C is System
Volume Serial Number is 303E-6F73
Directory of C:\oracle\product\product\11.1.0\db_1\database
23/07/2009 00:01 2,560 PWDORACOS.ORA
1 File(s) 2,560 bytes
0 Dir(s) 5,201,625,088 bytes free
SQL> create user passw identified by passw;
User created.
SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
Volume in drive C is System
Volume Serial Number is 303E-6F73
Directory of C:\oracle\product\product\11.1.0\db_1\database
23/07/2009 00:01 2,560 PWDORACOS.ORA
1 File(s) 2,560 bytes
0 Dir(s) 5,201,625,088 bytes free
SQL> alter user passw identified by passw;
User altered.
SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
Volume in drive C is System
Volume Serial Number is 303E-6F73
Directory of C:\oracle\product\product\11.1.0\db_1\database
27/07/2009 16:47 2,560 PWDORACOS.ORA
1 File(s) 2,560 bytes
0 Dir(s) 5,201,625,088 bytes free
Footnote:explanation below came via mail from a reader and looks logical.
—-
Anytime a password is changed Oracle opens the password file to check if the user is a dba user and if so it updates the file. If not, obviously oracle does not update it. When oracle opens the file to do the check, oracle does that in read/write mode rather than in read only mode. This is because if the user does happen to be a DBA user it will be more work by closing the file in read mode and reopening in read/write mode and then perform the update.
