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
s/e/c/ in my last name 😉
Comment by laurentschneider — March 13, 2009 @ 9:11 am
Ooops sorry for that. Checked 5 times before I wrote, and still wrong 🙂
Comment by coskan — March 13, 2009 @ 10:38 am
Tks for the tip this procedure is really a save life
Comment by Nomura — January 6, 2010 @ 11:35 pm
Hi Coskan, its interesting and is the easiest option.
Thanks,
Sasi
Comment by Sasidhar B — August 10, 2011 @ 6:47 am
Hi,
The table sys.user$ contains the hashed password as well.
Comment by Mette Evert — May 16, 2012 @ 9:12 am
problem is you will be needing sysdba where not everybody has got access
Comment by coskan — May 16, 2012 @ 7:02 pm
[…] ~]$ sqlplus /nolog @ > connect prueba/prueba Conectado. Fuente Be Sociable, Share! Tweet […]
Pingback by Tips DBA, Linux and Others Things » Cambio temporal del password a un USER — September 8, 2012 @ 1:45 pm
[…] ~]$ sqlplus /nolog @ > connect prueba/prueba Conectado. Fuente Oracle […]
Pingback by TIPS DBA, LINUX AND OTHERS THINGS » Cambio temporal del password a un USER — September 26, 2012 @ 7:00 pm
Cool! I wouldn’t have thought about that approach, but it is indeed much easier than the old way with USER$ Thank you for sharing it, Coskan!
Kind regards
Uwe
Comment by Uwe Hesse — February 21, 2013 @ 10:25 am
Allah razı olsun Coşki! 😉
Comment by Mehmet Bekir Birden — July 17, 2013 @ 8:17 am
Yalnız bir de script’e şunu eklemek şukela olur; set long 90000
Comment by Mehmet Bekir Birden — July 17, 2013 @ 8:21 am
whoah this blog is excellent i love reading your articles.
Keep up the great work! You understand, a lot of individuals
are searching round for this information, you can aid them
greatly.
Comment by support.gigatms.com.tw — May 15, 2014 @ 1:27 am