How to Change Oracle Database User Password?

Scenarios which require password Change:

1. Its recommended to change passwords periodically for security concerns.

2. User has forgotten/lost the password and unable to login to the system.

3. User wants to change password for any operational Purpose


Oracle database allows user to change database user's password using the below queries

+Logging in as the user

SQL> ALTER USER ME IDENTIFIED BY <NEW_PASSWORD>;

+Logging in as SYS/SYSTEM User

SQL> ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>;


There can be some cases when the DBA need to change password and revert back to older value after the dba action is completed.
Below test case helps in understanding how to change db user password and restore to older password value in Oracle 10g.

1. Change oracle user password using alter command

 SQL> conn system/manager
 Connected. 

SQL>  alter user TESTUSER identified by password1 
 User altered. 

2. Test the password is working

SQL> conn TESTUSER/password1;
 Connected.

3.Retreive the encrypted password for user

SQL> conn system/manager; 
Connected.

SQL> alter user TESTUSER identified by values '6057000499B128C3'; 
User altered. 

select username, password from dba_users where username = 'TESTUSER'; 
  
USERNAME                       PASSWORD 
------------------------------ ------------------------------ 
TESTUSER                            DB78866145D4E1C3 


4. Change the value of TESTUSER password to a new value

SQL> conn system/manager
Connected. 
SQL>  
SQL>  alter user TESTUSER identified by password2; 

User altered. 

 5. Verify the new password is working

SQL> conn TESTUSER/password2; 
Connected. 

6. Restore the older password using the encrypted password which we retreived from dba_users;
SQL> conn system/manager@dev; 
Connected. 
SQL> alter user TESTUSER identified by values 'DB78866145D4E1C3'; 

User altered. 

7. Verify that you are able to connect using old password.

SQL> conn TESTUSER/password2@dev;

ORA-1017 invalid username/password ;logon denied

SQL> conn TESTUSER/password1@dev;
Connected. 

How to Purge Workflow Notifications from Mailer Queue, so that Email is not sent to Users?

What to do when Workflow Notification  Mailer was not running properly for a certain period of time and accumulated all the emails in Workflow Queue.
We can Purge the workflow Mails which are not required by updating mail_status='SENT' for unwanted notifications in wf_notifications table.

Here are a couple of reasons to Purge Workflow Notifications

a)The end user may not want to receive outdated email.
b)The Workflow Queue has accumulated a lot of emails during the issue period, and processing has become very slow.

Below are the steps to Purge Workflow Notifications which are not required

1.First Take a Backup of wf_notifications table  and stop the Workflow Notification Mailer

2. Verify notifications from WF_NOTIFICATIONS table that has potential for being sent

SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     where status in ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     order by notification_id;
   
3. Narrow down the emails which are not to be sent by the Mailer from a specific date range.

SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     Where Status In ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     and begin_date < sysdate-30              -- List only emails older than 30 days ago
     order by notification_id;

4. Set Mail_status='SENT' in WF_NOTIFICATIONS

SQL>update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED')
and begin_date < sysdate-30;      

SQL>Commit;


This will update  notifications ,which are older than 30 days and waiting to be sent by the mailer to SENT.
Therefore the notifications will not get emailed when the Mailer is restarted.

5.Run the script $FND_TOP/patch/115/sql/wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.  It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer. (or CANCELED and INVALID if certain concurrent reports are run)

Example :
$ sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr


Example Syntax:
$ sqlplus apps/***@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps ***** applsys


6.Now start the Workflow Notification Mailer.