What is the meaning of the "10G 11G" value in DBA_USERS.PASSWORD_VERSIONS?

When the value of DBA_USERS.PASSWORD_VERSIONS is shown as "10G 11G", it means that both old and new-style hash values are available for the user.

Note that instead of storing the hashed password values directly in the DBA_USERS table, they are from 11gR1 and onwards both stored in the table USER$, column PASSWORD for the 10G style hash value, and column SPARE4 for the 11G SHA-1 style hash value.

A NULL value indicates that the password has not been changed since the migration and the user still has the old case insensitive password.

In my query below, USER1 through USER4 have been migrated and will now take advantage of 11g password case-sensitivity, if enabled.

USER5 is still using 10G style case insensitive passwords.

USER6 is created after migration, and will also take advantage of the 11g password case-sensitivity, if available.

SELECT U.NAME "Name",
            NVL(REGEXP_REPLACE(U.SPARE4,'^.+', 'Password changed since migration',1,0), 'Password unchanged since migration') "Action undertaken",
            DU.PASSWORD_VERSIONS "Password Version"
FROM USER$ U LEFT OUTER JOIN DBA_USERS DU
ON U.NAME = DU.USERNAME
WHERE DU.USERNAME NOT IN (SELECT ROLE FROM DBA_ROLES)
ORDER BY NAME ASC;

Name	Action undertaken	Password Version
USER1	Password changed since migration	10G 11G
USER2	Password changed since migration	10G 11G
USER3	Password changed since migration	10G 11G
USER4	Password changed since migration	10G 11G
USER5	Password unchanged since migration	10g
USER6	Password changed since migration	11G

Users that are imported from an earlier release into an 11g database, will remain case-insensitive until the password is changed.


To generate "alter user identified by values" statements, use the following:

SELECT 'alter user ' || NAME || ' identified by values ' || '''' || SPARE4 ||';' || PASSWORD ||''';' 
FROM USER$ 
WHERE  NAME IN ('USER1','USER2');

Sources: Oracle Documentation