I’ve gotten tired of dealing with this on various development databases so I wrote a script to automagically remove password expiration and unlock all schema accounts.
Below is the script – verified working as of Oracle 12c
declare sql_statement varchar2(4000); BEGIN FOR profileObject IN (select DISTINCT profile from DBA_USERS) LOOP BEGIN sql_statement := ' alter profile ' || profileObject.PROFILE || ' limit password_life_time UNLIMITED'; execute immediate sql_statement; dbms_output.put_line('SQL Statement Succeeded - ' || sql_statement); COMMIT; EXCEPTION when others then dbms_output.put_line('SQL Statement Failed - ' || sql_statement); END; END LOOP; FOR sysUserObject IN (select * from sys.user$) LOOP BEGIN sql_statement := 'ALTER USER ' || sysUserObject.name ||' IDENTIFIED BY VALUES '''|| sysUserObject.spare4 ||';'|| sysUserObject.password || ''''; execute immediate sql_statement; dbms_output.put_line('SQL Statement Succeeded - ' || sql_statement); COMMIT; EXCEPTION when others then dbms_output.put_line('SQL Statement Failed - ' || sql_statement); END; END LOOP; FOR userObject IN (select DISTINCT USERNAME from DBA_USERS) LOOP BEGIN sql_statement := 'alter user ' || userObject.USERNAME || ' account unlock'; execute immediate sql_statement; COMMIT; dbms_output.put_line('SQL Statement Succeeded - ' || sql_statement); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('SQL Statement Failed - ' || sql_statement); END; END LOOP; COMMIT; END;