You probably know that on Oracle 11G, hash values of passwords, are no longer shown in dba_users table, so to change the password temporarily, it is not possible to use the way I explained here before.
On Oracle 11G, there is one method posted by Laurent Schneider which is, to use SYS.USER$ table to extract hash value of the password.
Another method, I tried and got success, is using DBMS_METADATA.GET_DDL function without going to any other dictionary tables.
DBA Session — check the output of DBMS_METADATA.GET_DDL
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> drop user coskan cascade;
User dropped.
SQL> create user coskan identified by oracle;
User created.
SQL> grant create session to coskan;
Grant succeeded.
SQL> select dbms_metadata.get_ddl('USER','COSKAN') from dual;
DBMS_METADATA.GET_DDL('USER','COSKAN')
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
CREATE USER "COSKAN" IDENTIFIED BY
VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
As you see gathering DDL of the user gives us the hash value
On the User session —try connection
SQL> connect coskan/oracle Connected. SQL>
DBA Session —change user password
SQL> alter user coskan identified by msssql; User altered.
User Session —connect with new password
SQL> connect coskan/msssql Connected.
DBA Session — Update the user password with the value you gathered by DBMS_METADATA.GET_DDL
SQL> alter user coskan identified by 2 VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'; User altered.
User Session – Try the old password
SQL> connect coskan/oracle Connected. SQL>
I think this option is a bit easier than extracting from table approach.
Update-01/12/11 After I read the question on this oracle-l question http://www.freelists.org/post/oracle-l/11202-setting-password I think I need to mention that you need to have “SET LONG 999999″ setting for your sqlplus env
