By default oracle clinical or RDC users are prompted to change the password in every 3months. If the user doesn't log in within those 90 days then his password expires then it's only the DBA who can reset the password.
If you are looking for the option to email the users - > you have to have your own custom code .
enable the audit trail to DB level. sys.aud$ will store all the login details - you can schedule a job to run every day and see if users didn't login for > 90 days send out email.
If you are only looking for the way to lock the account then its gonna be easy - create a new database profile with the settings you need.
How does one enforce strict password controls?
By default Oracle's security is not extremely good. For example, Oracle will allow users to choose single character passwords and passwords that match their names and userids. Also, passwords don't ever expire. This means that one can hack an account for years without ever locking the user.
From Oracle 8 one can manage passwords through profiles. Some of the things that one can restrict:
* FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked
* PASSWORD_LIFE_TIME - limits the number of days the same password can be used for authentication
* PASSWORD_REUSE_TIME - number of days before a password can be reused
* PASSWORD_REUSE_MAX - number of password changes required before the current password can be reused
* PASSWORD_LOCK_TIME - number of days an account will be locked after maximum failed login attempts
* PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed
* PASSWORD_VERIFY_FUNCTION - password complexity verification script
Look at this simple example:
read http://www.orafaq.com/wiki/Oracle_database_Security_FAQ for more details.