PROBLEM: ORA-28007: the password cannot be reused (SAMPLE USER SCOTT)
=====================================================================

1. IDENTIFY SCOTT'S PROFILE
---------------------------
select profile from dba_users where username = 'SCOTT';
---> MYPROF


2. IDENTIFY RELEVANT RESOURCE LIMITS
------------------------------------
select * from dba_profiles where profile = 'MYPROF' and resource_name = 'PASSWORD_REUSE_MAX';
select * from dba_profiles where profile = 'MYPROF' and resource_name = 'PASSWORD_REUSE_TIME';


3. SET RESOURCE LIMIT TO DEFAULT FOR RESOURCE <PASSWORD_REUSE_MAX>
------------------------------------------------------------------
alter profile MYPROF limit PASSWORD_REUSE_MAX default;
alter profile MYPROF limit PASSWORD_REUSE_TIME unlimited;


4. SCOTT'S PASSWORD SHOULD BE ALTERABLE NOW...
----------------------------------------------
alter user SCOTT identified by tiger account unlock;


5. REVERT BACK THE LIMITS
=========================
alter profile MYPROF limit PASSWORD_REUSE_MAX unlimited;
alter profile MYPROF limit PASSWORD_REUSE_TIME 360;


=============
|   W E B   |
=============
CONFIGURING AUTHENTICATION: http://docs.oracle.com/cd/E11882_01/network.112/e36292/authentication.htm#DBSEG003
DBA_PROFILES..............: http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4187.htm#REFRN23169
MISC......................: http://www.dba-oracle.com/t_ora_28007_password_cannot_be_reused.htm

________________________________________________________________________________
________________________________________________________________________________


DECLARE
   v_profile             VARCHAR2(32);
   v_pw_reuse_max        VARCHAR2(32);
   v_pw_reuse_time       VARCHAR2(32);
   v_pw_reuse_max_def    VARCHAR2(32);
   v_pw_reuse_time_def   VARCHAR2(32);
   v_sql                 VARCHAR2(2000);
BEGIN
   select profile into v_profile from dba_users    where username = upper('&&username');

   select limit   into v_pw_reuse_max  from dba_profiles where profile = v_profile and resource_name = 'PASSWORD_REUSE_MAX';
   select limit   into v_pw_reuse_time from dba_profiles where profile = v_profile and resource_name = 'PASSWORD_REUSE_TIME';
 
   select limit   into v_pw_reuse_max_def  from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_REUSE_MAX';
   select limit   into v_pw_reuse_time_def from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_REUSE_TIME';
 
   v_sql := 'alter profile ' || v_profile || ' limit PASSWORD_REUSE_MAX default';
      execute immediate v_sql;
   v_sql := 'alter profile ' || v_profile || ' limit PASSWORD_REUSE_TIME unlimited';
      execute immediate v_sql;
   v_sql := 'alter user &&username identified by &new_password account unlock';
      execute immediate v_sql;
   v_sql := 'alter profile ' || v_profile || ' limit PASSWORD_REUSE_MAX default';
      execute immediate v_sql;
   v_sql := 'alter profile ' || v_profile || ' limit PASSWORD_REUSE_TIME unlimited';
      execute immediate v_sql;
END;
/
________________________________________________________________________________
________________________________________________________________________________