Before starting, here is the deal: While “ALTER USER simon PASSWORD EXPIRE” exists, the “PASSWORD UNEXPIRE” statement does not exist in Oracle, see the ALTER USER statement in the language reference. Now, there is a good reason that command does not exist.
One problem that occasionally pops up is that a DBA needs to recreate a user with a password that he does not know. This happened to me when I had to create a schema with the same password on a development database. After I set an initial password for the developer, he exclaimed that he wanted the same password that he had for the schema X, but he did not want to tell me the password (don’t ask here, that’s a completely other story).
One way to do this is to use ALTER USER ... IDENTIFIED BY VALUES. Using the excellent article on the ALTER USER commands from Laurent Schneider, we can generate a dynamic SQL query to set a password without knowing the password itself!
Here is the query to read the password from sys.user$ from Laurents article:
My name is Simon Krenger, I am a Technical Account Manager (TAM) at Red Hat. I advise our customers in using Kubernetes, Containers, Linux and Open Source.