In the actual work of the DBA, often encounter some situation, for example, I do not know a user's password, but I need to log in to the database through this user to do some action, but also must be logged through this user, such as a user to create a private db Link
Or to migrate some data to the new database, the developer requirements and the original database password consistent, we go to collect the password trouble is also prone to error, this time how to do? The ALTER USER statement from Oracle provides a identified by values clause that allows the DBA to modify the password directly using the ciphertext stored in the database dictionary without knowing the plaintext of the password.
For example, I need to log in a user, and then change back to the original password, as follows:
Sql>select PASSWORD from sys.user$ where name = ' UT001 '; --Get the current user's password ciphertext sql>alter user UT001 identified by 123456; --Modify the user's password for 123456sql>alter user UT001 identified by values ' S: C7C81BBE7760B5BBB3973F0971AA36C737BF6DCC4A34FE925CE70B0739BD '; --Change the password to the original password.
2. To migrate the database environment, create a user name and password consistent with the source library in the new library as follows:
Sql>select ' Create user ' | | name | | ' identified by Values ' | | password | | '; ' from sys.user$ where name in (' UT001 ', ' UT002 ' ...);
Using the concatenation of good SQL directly in the new library execution can, of course, can also make profile,deftablespace,temptablespace and so on information.
This article is from the "Database Road" blog, make sure to keep this source http://dbaway.blog.51cto.com/7099215/1683986
Tips for small tricks use identified by values to reset user passwords