How to change the user name in Oracle

Many people think that Oracle can easily change the password but cannot change the username. actually, the name can be changed. it is a little troublesome to change. the use of username change is useful in such a scenario. assume that a company has an account for each employee. The user name is your English name. when arwen of an employee is about to resign, it is about to delete arwen. however, we know that users in oracle are fully bound to the objects created under this user, and objects such as tables in SQL server can be completely separated from users. in this case, when you delete the user arwen, all the data below will be lost. if we need the data, the easiest thing to think of is to export all the data first and then import it to a user. however, this is troublesome. so it would be better to change the name. www.2cto.com how to change the user name and then use the following example. suppose we have a new employee, weiwenhp. he is from arwen's class. therefore, he needs the data below arwen. in this case, change the user arwen User weiwenhp. when we log on to the database, the database system will judge whether our user name and password are correct or not. Then we naturally think of where the information must be stored in the database, there must be a table storing user information. yes, so the user information is stored in the table user $. select * from user $ www.2cto.com with this SQL query, you will see all the usernames and passwords, there are other information. however, user names are encrypted. therefore, even the Administrator does not see any user's password and can only change the user's password. I must have changed the user name and password here. in fact, this is the case. select user #, name from user $ where name = 'arwen'; -- first look at the ARWEN information, where user # is a serial number, which is equivalent to an ID card number, suppose here is 250 update user $ set name = 'weiwenhp 'where user # = 250; -- The user name is changed. of course it's an update statement. Remember to submit it again at commit. what else do you want to do? After you change your name, you will happily try logging in. as a result, the website cannot be accessed. however, you can log on with the previous account arwen. then you are dumb. what's going on? I know that if I change the password, it will take effect immediately. If I change the name, I won't. we know that the important thing in the database is to ensure data consistency. The data in a certain place has been changed, and the relevant areas have to be changed. it must be because the user information read during login has not been updated. data inconsistency may occur. you may think that sometimes we have to restart the database to change some parameter information with alter to take effect. so you don't think I have to restart the database after changing the name. this is terrible. in fact, you do not need to restart the database to pull it, so you can force update it. alter system checkpoint; alter system flush shared_pool; you can log on again and find that you can log on. the previous arwen users could not log on. however, it is not convenient to change the user name like above. It is estimated that few DBAs do this. there are not many scenarios to change the user name. and it can be done even if you want to change the pilot output and then import it. what do you want to do is display the password in plaintext. You just found that both the user name and password are in the same table, and the password is encrypted in the ciphertext, so you think I can't try to make it into plaintext. in this way, you can see other people's passwords. so you want to use the same name change method www.2cto.com update user $ set password = 'abc' where user # = 250; alter system checkpoin; alter system flush shared_pool; select * from user $ where name = 'arwen' -- now you find that the password turns into plain text, abc. so you have a bad cola, so you can't wait to log on to it to see if it can be used. as a result, you are frustrated and cannot log on. in addition, the original password of arwen is also decommissioned. therefore, you can only use alter to change it. www.2cto.com alter user arwen identified by abc. it cannot be logged on after being changed to plain text, but no error is reported during the modification. I don't know what is behind oracle. I think it is better to prompt that the password in the table user $ cannot be changed. however, no administrator will be idle enough to show the plaintext of the password. so it doesn't matter. I use oracle 11gr1. I don't know what the oracle version could do in the early stage. I could change the password to plain text, but I couldn't log on again.