select
username,
expiry_date,
password_change_date,
last_login
from
dba_users
where
account_status = 'EXPIRED';
select
prf.resource_name,
prf.limit,
usr.account_status,
usr.password_change_date,
usr.expiry_date,
usr.last_login,
usr.profile,
usr.password_versions,
usr.authentication_type
from
dba_users usr join
dba_profiles prf on usr.profile = prf.profile
where
prf.resource_type = 'PASSWORD' and
usr.username = 'RENE';
Changing password in SQL*Plus
An expired password can be changed with SQL*Plus: this tool will ask for a new password when it receives an ORA-28001.
$ development/databases/Oracle/SQL-Plus[sqlplus] rene/…@ora19
…
ERROR:
ORA-28001: the password has expired
Changing password for rene
New password: …
Retype new password: …
Password changed
…
Changing a password with ALTER USER privileges
A user having the alter usersystem privilege can change a user's password:
alter user rene identified by theNewSecretPassword;
Altering the password expiration policy
«Create» the alter profile statement with which the profile of a given user can be altered so that the password life is infinite.
select 'alter profile ' || profile || ' limit password_life_time unlimited;' from dba_users where username = 'RENE';
Executing the statement will change the password expiration policy for all users with this profile! This might or might not be what is intended.
alter profile DEFAULT limit password_life_time unlimited;
Executing the alter profile statement does not automatically unlock the user.