In 11g the Oracle the hash password is no longer stored in DBA_USERS, it is stored in SYS.USER$ table in the column “PASSWORD” and “SPARE4”. So there are different ways password can be set depending on if “PASSWORD” and “SPARE4” are set in SYS.USER$ and what you want PASSWORD_VERSIONS to be.
If only “SPARE4” is used to set the password the password version is 11g and in SYS.USER$ password value becomes NULL after ALTER statement
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
——————————————————————————–
F894844C34402B67
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G 11G
SQL> alter user scott identified by values 'S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346';
User altered.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
———————————————————————–
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346
If both password and SPARE4 is used to restore the password, the PASSWORD_VERSIONS is “10G 11G”, to set this the IDENTIFIED BY VALUES is passed in as “SPARE4;PASSWORD” from sys.user$
SQL> alter user scott identified by values 'S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B;F894844C34402B67';
User altered.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G 11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
——————————————————————–
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B
If the value from PASSWORD is used from SYS.USER$ the password version is set to 10g and column SPARE4 is set to NULL
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
————————————————————————-
F894844C34402B67
alexlima.com 