In practice, 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, 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 (this method is passed in Oracle9i)
SQL> UPDATE USER $ SET NAME = 'TT' WHERE USER # = 91;
1 row updated.
SQL> COMMIT;
Submitted.
SQL> ALTER SYSTEM CHECKPOINT;
The system has been changed.
SQL> ALTER USER TT IDENTIFIED BY VALUES '294ce6e71_dd890 ';
Alter user tt identified by values '294ce6e71_dd890'
*
ERROR is located in row 1st:
ORA-01918: User 'TT' does not exist
Force Oracle to read actual data, rather than read the cache
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
The system has been changed.
SQL> ALTER USER TT IDENTIFIED BY VALUES '294ce6e71_dd890 ';
The user has changed.
Test connection
SQL> CONN TT/TT
Connected.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
-----------------------------------------------
TEST_TT TABLE
SQL> CONN/AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"
SQL> SHUTDOWN IMMEDIATE
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> STARTUP
The ORACLE routine has been started.
......
The database has been loaded.
The database has been opened.
It can be seen that TEST is not recovered.
SQL> SELECT USER #, NAME, PASSWORD FROM USER $ WHERE USER # = 91;
USER # NAME PASSWORD
--------------------------------------------------------
91 TT 294ce6e7425dd890
SQL> CONN TT/TT
Connected.
View data objects
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
-----------------------------------------------
TEST_TT TABLE
SQL> SELECT * FROM TEST_TT;
A
-
A
The object permission is still valid.
SQL> SELECT * FROM TEST1.TEST1 _ TT;
A
-
A
The system permission is still valid.
SQL> CREATE TABLE KK AS SELECT * FROM TEST1.TEST1 _ TT;
The table has been created.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
-----------------------------------------------
KK TABLE
TEST_TT TABLE
SQL> CONN/AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"
-- No problem with DROP USER
SQL> DROP USER TT CASCADE;
User discarded
SQL> DROP USER TEST1 CASCADE;
User discarded