Re: See how long an account is locked

> On Aug 12, 11:20 am, lothar.armbrues..._at_t-online.de (Lothar
> Armbrüster) wrote:
>> Hello out there,
>>
>> I'm experimenting with account locking, password expiry and the like
>> on Oracle 10.2.0.4.
>> I've created a profile with PASSWORD_LOCK_TIME 1/24. So when a couple of
>> logins fail, the account is locked for one hour. This works as expected. In
>> the Enterprise Manager I see the account as LOCKED(TIMED).
>> Now the question is, is there a way to determine the time remaining that the
>> account is locked. DBA_USERS does not have a column showing that. (Or at least
>> I'm unable to find it ;-)
>>
>> Knowing the overall time the account is locked, it would suffice to know when
>> the account got locked. But it did not find this information also.
>>

[...]
>
> It does, and it's named LOCK_DATE. Properly formatted you'll also see
> the time and can then report the length of time the lock has been in
> force:
>
> select username, account_status, lock_date, round((sysdate - lock_date)
> *1440, 2) lock_minutes
> from dba_users
> where account_status like '%TIMED%';
>
>
> You can also do this:
>
> column limit new_value locklim
>
> select round(limit*1440, 0) limit
> from dba_profiles
> where profile = 'SMING'
> and resource_name = 'PASSWORD_LOCK_TIME'
> /
>
> select username, account_status, lock_date, round((sysdate - lock_date)
> *1440, 2) lock_minutes,
> &locklim - round((sysdate - lock_date)*1440, 2) time_left
> from dba_users
> where account_status like '%TIMED%'
> /
>
>
> David Fitzjarrell

Hello David,
many thanks for your hints. I was looking for LOCK_EXPIRE_TIME or something like this. My gaze was too narrow to see the LOCK_DATE. ;-)

Regards,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |