Password retrieve and delete

To revtrieve password in encoded format

select
   dbms_metadata.get_ddl('USER', username) || '/' usercreate
from
   dba_users where USERNAME='UVODS';
 
output:
CREATE USER "UVODS" IDENTIFIED BY VALUES 'S:E2D1A00B07029CE6707D07D042AE14F8


Also the following yields the encoded password

SELECT name,spare4 FROM SYS.USER$ WHERE NAME='UVODS';


****************************************************************************************************************************
In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.

SQL> select name,password from sys.user$ where username='SCOTT';

USERNAME PASSWORD
-------- ----------------
SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;


Now login with the following credentials: scott/tiger
After doing your work you can change the password back by using an undocumented feature called "by values"

SQL> alter user scott identified by values 'F894844C34402B67';

****************************************************************************************************************************

Oracle stores history passwords in select * from sys.user_history$;

we can get the user name from the above view basing on user id

SELECT username, user_id FROM dba_users WHERE username='SVADAPALLI'

From here we can delete the history of particular user from history and use the same password again

DELETE FROM user_history$ WHERE user#=42

****************************************************************************************************************************

select
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password
from
   dba_users
where
username = 'PEDQ_OPSS';