Oracle SQL Utility Script for Unlock of All Accounts and Removal of Password Expiration

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s