Oracle 12c UNLOCK ALL LOCKED users in one shot

Question

Hi I want to UNLOCK a list of users from dba_users of my Oracle 12C.

I Tried Like:

ALTER USER (SELECT username FROM dba_users WHERE ACCOUNT_STATUS LIKE '%LOCKED%') ACCOUNT UNLOCK;

ERROR at line 1:
ORA-01935: missing user or role name

Any Idea ? Or I have to do manually for each user?

Answer 1

One option is to write a query which will write query for you. For example:

SQL> select 'alter user ' || username || ' account unlock;' from dba_users;

'ALTERUSER'||USERNAME||'ACCOUNTUNLOCK;'
---------------------------------------------------------
alter user SUPERUSER account unlock;
alter user C##TEST account unlock;
alter user SYS account unlock;
alter user SYSTEM account unlock;
alter user ANONYMOUS account unlock;
alter user SUSHANT account unlock;
alter user SCOTT account unlock;
<snip>

Now, copy/paste the above bunch of ALTER USER statements and you'll unlock everyone.

---

Another is a simple PL/SQL block which uses dynamic SQL (I'm unlocking only user SCOTT; you'd unlock anyone you want).

SQL> begin
  2    for cur_r in (select username from dba_users where username in ('SCOTT')) loop
  3      execute immediate 'alter user ' || cur_r.username || ' account unlock';
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>