Posted by: prankster April 8, 2011
sql help
Login in to Rate this Post:     0       ?        
IMO your original sql should work. Only thing is you dont have any verify_function created. Thats why it is displaying nothing.
Once you run utlpwdmg.sql and then assign particular function to the profile. The limit should have the funciton name . You can verify this by using following sql,

SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile='DEFAULT'and resource_name='PASSWORD_VERIFY_FUNCTION';

Currently you'll have something like below since no verify function is assigned to a profile.
PROFILE                        RESOURCE_NAME                    RESOURCE_TYPE LIMIT                                   
------------------------------ -------------------------------- ------------- ----------------------------------------
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD      NULL                                    

Once you apply password verification function to the profile using
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;


The above output would look something like below
PROFILE                        RESOURCE_NAME                    RESOURCE_TYPE LIMIT                                   
------------------------------ -------------------------------- ------------- ----------------------------------------
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD      verify_function

Then your original should work. I've not tested it out myself, neither done it before, but did some search. And this makes sense to me. There might be better answers from DBAs. Let us know if u find better soln.
Last edited: 08-Apr-11 12:13 PM
Read Full Discussion Thread for this article