Step 4: Change the Oracle user name

Oracle user name change is a rare requirement for Oracle user name modification in four steps, but it is still a required knowledge for beginners, here, we use four simple steps to change and replace the Oracle user name. AD: 2013 cloud computing architect Summit course materials download our Oracle management work often involves changing Oracle users

Oracle user name change is a rare requirement for Oracle user name modification in four steps, but it is still a required knowledge for beginners, here, we use four simple steps to change and replace the Oracle user name. AD: 2013 cloud computing architect Summit course materials download our Oracle management work often involves changing Oracle users

Step 4: Change the Oracle user name

Modifying the Oracle user name is not a common requirement, but it is still a required knowledge for beginners. Here, we use four simple steps to change and replace the Oracle user name.

AD: 2013 cloud computing architect Summit course material download

Our Oracle management work often involves changing the attributes and passwords of Oracle users. However, in some application scenarios, how can we solve this problem? The following four steps are used to modify the Oracle user name.

1. query and change the Oracle user name

 
 

  
  
SQL> select user#,name,password from user$ where name ='TICKETS'; 
  
  
 
  
  
 USER# NAME PASSWORD 
  
  
---------- ------------------------------ ------------------------------ 
  
  
 78 TICKETS 21EDA78F89D6FACD 
 
 

Ii. Change User Name

 
 

  
  
SQL> update user$ set name='TICKETS_BAK' where user#=78; 
  
  
 
  
  
1 row updated. 
  
  
 
  
  
SQL> commit; 
  
  
 
  
  
Commit complete. 
 
 

3. Create the same Oracle user name

 
 

  
  
SQL> create user tickets identified by "123456" 
  
  
 2 default tablespace yytickets 
  
  
 3 temporary tablespace temp; 
  
  
create user tickets identified by "123456" 
  
  
 * 
  
  
ERROR at line 1: 
  
  
ORA-01920: user name 'TICKETS' conflicts with another user or role name 
 
 

The system prompts "User Name Conflict" and runs the following SQL statement.

 
 

  
  
SQL> alter system checkpoint; ---- force write to the data file, that is, let the user $ result (dirty block) modified in step 2. Change the user name) user $ written to the data file
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> alter system flush shared_pool; ---- when you create user tickets in the Next Step (4. create the same user name), the oracle system automatically modifies the system table user $. And here
Clear the data dictionary information in the cache (user $ ), the oracle system will forcibly read the actual data in user $ on the data file before modifying the system table user $ (that is, the changed data)
  
  
 
  
  
System altered.
 
 

4. Create the same user name

 
 

  
  
SQL> create user tickets identified by "123456" 
  
  
 2 default tablespace yytickets 
  
  
 3 temporary tablespace temp; 
  
  
 
  
  
User created. 
  
  
 
  
  
SQL> grant connect,resource to tickets; 
  
  
 
  
  
Grant succeeded. 
  
  
 
  
  
SQL> commit; 
  
  
 
  
  
Commit complete. 
 
 

In this way, you can find, change, replace, and copy Oracle user names. You need to check and resolve user name conflicts to prevent future risks.

[Edit recommendations]

1. Oracle username re-indexing method
2. Explain how to query user tablespaces in Oracle
3. An analysis of the management method of the Oracle user permission table
4. Introduction to Oracle database backup and recovery features
5. Five restrictions on using Oracle External tables