Changing the Oracle Database User Password Expiration

Oracle Database - 11.2.0.4.0

1. Show the user's profile.

   SELECT username, profile FROM dba_users WHERE username='EXFSYS';

2. Show the profile's password policy.

   SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name LIKE 'PASSWORD%';

3. Change the password life time from the default 180 days to unlimited.

   ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

4. Reset user password.

   ALTER USER exfsys IDENTIFIED BY abc123;
   
   -- for 10g
   SELECT 'ALTER USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''';' FROM dba_users WHERE account_status='OPEN';
   
   -- for 11g
   SELECT 'ALTER USER ' || name || ' IDENTIFIED BY VALUES ''' || password || ''';' FROM sys.user$ WHERE name IN (SELECT username FROM dba_users WHERE account_status='OPEN');

5. User unlock.

   ALTER USER exfsys ACCOUNT UNLOCK;