-- to unlock an account
alter user xxx account unlock;

-- expired account
alter user xxx indentified by xxx;

-- change the password expiry for a profile, in this case the default profile:
alter profile default limit password_life_time unlimited;

-- list account info
set lines 200
column username format a30
column profile format a30
select username,profile, account_status, lock_date, expiry_date from dba_users order by 3;

-- To resolve a password expiry without resetting the password, then past the resultset
 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 like 'DBSNMP';

-- the above will reset the password to the existing password and un-expire the account