Ask the Expert

Oracle 10g database locking when users log out of ADP software

Are there any known issues with the Oracle 10g database locking when users log out of ADP software and not allowing them to log back in until a script is run to unlock the database? If so, is there a fix?

    Requires Free Membership to View

From your description, it sounds like individual Oracle database accounts are being locked, not the database itself since there is really no way to lock the database as you've described. I have not seen any software behave in this manner. Is it possible that within the database there are profiles enabled (other than the default) to manage password expiration and complexity.

If this is the case, an account password would expire by a certain date (depending on what the frequency is set to) and the user must change their password. If the user does not change their password, and the grace period expires, the database account is locked preventing the user from logging in until the account is unlocked by a DBA. If there are complexity rules in place, the password must be a certain length, and contain numbers of other special characters. If password expiration and complexity are used within the database, the application software must be able to handle the Oracle errors that are generated so that the message is relayed back to the user and he or she can take appropriate action.

To determine whether profiles have been created, run the following query in SQL*Plus as a privileged or DBA user:

select distinct profile from dba_profiles;

If you get back more than just DEFAULT for a profile name, then there have been profiles created. To find out what limits have been set for each profile, you can run the following query:

select resource_name, resource_type from dba_profiles
where profile_name='<name of profile>';

To determine where a database account has been locked, run the following SQL:

select username, account_status from dba_users;

To unlock an account:

alter user <username here> account unlock;

This was first published in April 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: