APEX user account locked?

How to unlock your user accounts in Oracle APEX

Resetting your APEX user & Workspace admin passwords  

An easy way how you can reset your APEX user account lock flags directly from database.

So many times it happened to me that my user accounts got locked when entering the wrong passwords for more than three times. This is the the way I unlock my users. You need to have access to you sys accounts or you can be assisted by your DBA to do these steps.

Log into your database as sys user and run.

select * from APEX_040200.WWV_FLOW_FND_USER
where account_locked = 'Y'
and default_schema = 'XXXX'

As a result you will get all APEX accounts that are currently locked. What I must admit is I do knot know if this is a 'best way' to do it but it worked every time so far for me without any problems. All you need to do now is create an update statement to unlock your accounts.

update APEX_040200.WWV_FLOW_FND_USER
set account_locked = 'N'
where User_name in ('ADMIN')
and default_schema = 'XXXXX'

After this you should be able to login using same username and password as before.

In case you are using different APEX version all you need to do is change APEX_xxxxxx and it should work.

Apex user password reset

In case when you want to reset password for one of your APEX accounts you can use standard APEX_UTIL function. 

-- within APEX SQL WORKSHOP
BEGIN
    APEX_UTIL.RESET_PW(
        p_user => 'XXXXXX',
        p_msg => 'Your password has been reset by administrator.');
END;

In ideal world this function should send an email to this user and notify them of their new temporary password which will be asked to be changed on their first log in. Unfortunately it doesn't work every time, sometime users do not receive an email and sometime they are not asked to changed they passwords. I am really not sure why this happens but you have been warned. :)

APEX Workspace admin password reset

Log into your DB server as oracle user. The key thing now is to go to directory where your APEX installation folders are. In my case it was as simple as: 

C:\Users\oracle>d:

D:\>cd apex_4.2\apex

If you list all of your files in this folder you should be able to find apxxepwd.sql file which we will use to change our workspace admin password. 

.

.

.

22/10/2012  05:47 PM             1,853 apxrelod.sql
22/10/2012  05:47 PM             5,862 apxremov.sql
22/10/2012  05:47 PM             4,350 apxrtins.sql
22/10/2012  05:47 PM                44 apxsqler.sql
22/10/2012  05:47 PM             9,121 apxxemig.sql
22/10/2012  05:47 PM             3,271 apxxepwd.sql
22/10/2012  05:51 PM    <DIR>          builder

.

.

.

All you have to do now log in as sys to your database and run that script.

D:\apex_4.2\apex>sqlplus "/ as sysdba"

SQL> @apxxepwd

Once you run it you will be asked to enter your new password and that is all. Open again your http:\\ .......\apex\apex_admin link and enter your new password. Well done!!!!

If you think this is to complicated you can always stick with Apex admin interface under Administration tab and do it from there.  Of course you will need to have access with APEX administration privilege.

Thanks,

SL