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;
