How to get a list of locked users in Oracle

Products

• CA Business Service Insight

Issue/Introduction

When attempting to access the login screen, an Oracle error occurs, saying that the DB cannot connect. How can we check if we're locked out of the database?

There may also be strings errors, such as [MSG - Messages/General/ERROR_INTERNAL_ERROR_TEXT] 

Environment

Release:  8.3.5 and above
Component: CA Business Service Insight

Resolution

The following query will list all the users in the system and their lock status, so you can see if any need to be unlocked:

SELECT username,
account_status
FROM dba_users;

This should be done with the SYS admin account
If you do have any locked ones, then they can be unlocked and/or have their password reset with

 ALTER USER user_name IDENTIFIED BY password ACCOUNT UNLOCK;


Then you'll need to use the PassUpdate utility on both Web and App servers to ensure that BSI knows the correct password for that user:

   %OG_HOME%\Utilities\PassUpdate\PassUpdate.exe

You may need to check all the accounts that BSI uses and are shown in the PassUpdate utility. The following set of commands may be exeucted in SQLPlus to reset and unlock all the default users. Note that this should be done with the SYS database account, with each command executed one at a time to ensure successful execution

ALTER USER OBLICORE ACCOUNT UNLOCK;
ALTER USER USRSLA ACCOUNT UNLOCK;
ALTER USER USRMTN ACCOUNT UNLOCK;
ALTER USER USRCSL ACCOUNT UNLOCK;
ALTER USER USREDR ACCOUNT UNLOCK;
ALTER USER USRPSL ACCOUNT UNLOCK;
ALTER USER USRRPT ACCOUNT UNLOCK;
ALTER USER USRDASH ACCOUNT UNLOCK;
ALTER USER USRINSIGHT ACCOUNT UNLOCK;
ALTER USER OBLICORE identified by oblicore;
ALTER USER USRSLA identified by usrsla;
ALTER USER USRMTN identified by usrmtn;
ALTER USER USRCSL identified by usrcsl;
ALTER USER USREDR identified by usredr;
ALTER USER USRPSL identified by usrpsl;
ALTER USER USRRPT identified by usrrpt;
ALTER USER USRDASH identified by usrdash;
ALTER USER USRINSIGHT identified by usrinsight;

Additional Information

These account problems often occur because Oracle sets the default account profile to expire passwords after 30 days.
To change this and prevent it from happening again you can run:

alter profile DEFAULT limit password_life_time UNLIMITED;

 

Additional information regarding the above is available in PassUpdate in the BSI Documentation