How to modify the oracle user name

It is common to change the oracle user name. The steps for modifying the oracle user name are described in detail below. If you are interested in the oracle user name, take a look.

In actual work, you sometimes need to modify the user name, and the common practice is the exp imp method. If the data volume is large, the exp imp method will waste a lot of time, therefore, the following describes how to modify the user name by modifying the ORACLE base table:

-- Directly modify the underlying table USER $ to change the USER name

1. Run cmd on windows

2. sqlplus/nolog

3. SQL> connsys/lmis @ lmisdx_localas sysdba connects to the database. You can usually Log On with the sys user.

4. SQL> select * from user $; find the user whose name is to be modified #.

5. SQL> UPDATE USER $ SET NAME = 'new Username 'WHERE USER # = 38;
1 row updated.

6. SQL> COMMIT; the submission is complete.

7. SQL> ALTER SYSTEM CHECKPOINT;
The system has been changed.

8. SQL> ALTER USER new username IDENTIFIED BY new password;
The system prompts that the new user does not exist.

SQL> ALTER USER new username IDENTIFIED BY new password

* ERROR located in row 1st: ORA-01918: User 'new user' does not exist

9. SQL> ALTER SYSTEM FLUSH SHARED_POOL;
The system has been changed.

10. SQL> ALTER USER new USER IDENTIFIED BY new password;
The user has changed.

11. Test the connection
SQL> CONN new user/new password @ lmisdx_local;
Connected.

12. SQL> SELECT * FROM TAB;
Check whether the new user has the objects of the original user, including tables.

13. SQL> CONN/AS SYSDBA is connected.

14. SQL> SHOW USER -- view the current connected USER
Is "SYS"

15. SQL> SHUTDOWN IMMEDIATE -- close the database
The database has been closed. The database has been detached. The ORACLE routine has been disabled.

16. SQL> STARTUP -- start the database
The ORACLE routine has been started ....... The database has been loaded. The database has been opened.

17. We can see that TEST is not restored.
SQL> SELECT USER #, NAME, PASSWORD FROM USER $ WHERE USER # = 38;

18. View data objects
SQL> SELECT * FROM TAB;

19. SQL> connsys/lmis @ lmisdx_localas sysdba
Connected.
SQL> SHOW USERUSE
Is "SYS"
-- No problem with DROP USER
SQL> DROP USER TEST1 CASCADE; -- delete a previous USER
User discarded
 

Add Oracle user information in Linux

Measure the test taker's knowledge about the ORACLE user authentication mechanism.

Oracle tablespace statements

Default ORACLE user and password

Implementation of oracle multi-column subquery