Oracle password reuse policy is controlled by database profiles. Profiles are administered with CREATE PROFILE or ALTER PROFILE. Profiles are assigned to users with CREATE USER or ALTER USER. To set up password reuse you must specify the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX limits in the profile.
-- Can reuse after 5 days and 3 intermediate passwords
CREATE PROFILE reusable
LIMIT password_reuse_max 3
LIMIT password_reuse_time 5;
ALTER USER xero
PROFILE reusable;
If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to UNLIMITED, passwords can be reused freely. If one of these limits is set to UNLIMITED, and the other is set to an integer, the user cannot reuse passwords. If both limits are set to integers, then a password can be reused after the PASSWORD_REUSE_TIME number of days has expired and the password has been changed PASSWORD_REUSE_MAX number of times.
If either limit is set to DEFAULT, the limit value is taken from the DEFAULT PROFILE. The DEFAULT PROFILE initially has all limits set to UNLIMITED. Note that you can set the PASSWORD_REUSE_TIME to a time period less than a day by using fractions.
-- User must wait at least 1 hour before reusing password
ALTER PROFILE reusable
LIMIT password_reuse_time 1/24;
This information has been tested in Oracle 9i (release 9.2.0.8). It is also consistent with the official documentation for Oracle 10g (10.1).
