Hi All,
I have a lot of schema and generic users to follow up to avoid its password's expiration. I tried to find which of them are near to their expiration date but for some accounts my query didn't work...
SELECT USERNAME,
EXPIRY_DATE ,
PROFILE
FROM
(SELECT u.username ,
U.account_status ,
U.profile ,
P.limit AS passwd_exp_limit,
SYU.ptime ,
SYU.ptime + P.limit AS expiry_date
FROM dba_users U,
sys.user$ SYU ,
dba_profiles P
WHERE u.username NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'TSMSYS',
'DBSNMP', 'ORACLE_OCM', 'OPS$ORACLE')
AND SYU.user# = U.user_id
AND u.username NOT LIKE 'OPS$%'
AND p.profile = u.profile
AND p.resource_name = 'PASSWORD_LIFE_TIME'
)
WHERE TRUNC (EXPIRY_DATE) - 30 <= TRUNC(SYSDATE)
AND PROFILE <> 'END_USER';
It was the query's result.
USERNAME EXPIRY_DATE PROFILE
_________________________________________________
...
ETLDWDW 30-DEC-08 DEFAULT_PASSWORD_PROFILE
8 rows selected
But... It didn't expired at logon, even when according to my query it should be expired from a long time ago.
SQL> conn etldwdw@instance_name
Enter password: ***********
Connected.
SQL>
I checked the PASSWORD_LIFE_TIME for this profile, and it's equal to 90 days.
How can I determine which day will expire each account?
Thanks by your help
Martin
P.S: My version is
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production