well, I showed you how one tool can do it without having an alter command - in answer to "so I don't want the end users to use alter command, its
just that they get they run a script asking them for a new password."

in sqlplus, they would just type "password"

in other tools you may or may not have, you would read the documentation to see how and if they support a user changing their password.


As for the case where they forgot their passwords - you'll have to design and build something for that if you want it automated.

What I've seen in some places is that you would build a new application. It would have a single table

t( oracle_username, full_name, email, phone, ... etc )


your application would maintain this table - you could give them a web front end to maintain their personal information.

if they forgot their password, you can let them go to a screen where they type in their oracle_username, email address - and anything else you want to make them type in (maybe they have to answer one of those magic questions like what is your favorite color, your mothers maiden name, etc) - and your procedure would generate a password, alter their account, set it to expire really soon and email them this password.

or email them a link with a randomly generated identifier in it - that when clicked on - you would look up in a table and reset their password (to avoid people reseting other peoples password as a "joke"). This password reset would do what I outlined above at that point.

and you could of course expand this tiny application to have a change your password screen too.