Password change time

[jmodic]

thanks floyd didnt know about that one.

I found something bothersome.Look at the results from queries. The dba_ view is correct, but the sys.user$ expdate is wrong....

select t.username,t.expiry_date,
TO_date(t.expiry_date,'DD/MM/YYYY') - to_date(sysdate,'DD/MM/YYYY') "DAYS BEFORE EXPIRE"
from dba_users t
where t.username in('TEST_PROFILE','TEST_PROFILE_2')

USERNAME EXPIRY_DATE DAYS BEFORE EXPIRE
TEST_PROFILE_2 13/Nov/05 0224:33:23 PM 3
TEST_PROFILE 24/Nov/05 1024:45:20 AM 14


select name,ptime,exptime
from sys.user$
where name in ('TEST_PROFILE','TEST_PROFILE_2')

NAME PTIME EXPTIME
TEST_PROFILE_2 10/Nov/05 0224:33:23 PM 10/Nov/05 0224:31:16 PM
TEST_PROFILE 10/Nov/05 1024:45:20 AM

1. You are using TO_DATE() where you shouldn't (on DATE columns, like t.expiry_date and on sysdate) and on the other hand you don't use TO_CHAR() with appropriate format mask where you realy should (when displaying DATE columns).

2. You have some serious issues with your default date format mask - what kind of date is something like "10/Nov/05 0224:33:23 PM"

3. You should start using [ code ] tags to make results readable. This probably makes much more sense to readers:

NAME	              PTIME	                        EXPTIME
TEST_PROFILE_2	10/Nov/05 0224:33:23 PM	  10/Nov/05 0224:31:16 PM
TEST_PROFILE	 10/Nov/05 1024:45:20 AM

4. You are comparing apples and owen toasters with the above two queries, so it is not realy clear what you thing is wrong with your results. Look at the source of the DBA_USERS view and you'll find out how DBA_USERS.EXPIRY_DATE is defined and that it is not based only on USER$. If you'll still find your results to be strange, then you'll have to post some more information about your two users - at least their relevant values from PROFILE$.