How to change the schema or user name in Oracle

Developers have a requirement to move all objects in a schema to another shema. After thinking about it, simply change the schema name? In this way, you do not need to move the data. You are prepared to directly change the data using DDL statements:

1. Alter UserScott renameToScott2;

This statement was not found at all. After I checked it online, I found that Oracle itself did not provide this function, but there was a data dictionary table: user $, all users in this table can directly update the table and try to change it. It is quite useful and the implementation is as follows:

1. SQL>Desc User$;-- View the table structure www.bkjia.com
2.  NameNull? Type
3.  ---------------------------------------------------------------
5.  USER#NOT NULLNUMBER
6.  NAMENOT NULLVARCHAR2 (30)
7. TYPE #NOT NULLNUMBER
8.  PASSWORDVARCHAR2 (30)
9. DATATS #NOT NULLNUMBER
10. TEMPTS #NOT NULLNUMBER
11. CTIMENOT NULL DATE
12. PTIMEDATE
13. EXPTIMEDATE
14. LTIMEDATE
15. RESOURCE $NOT NULLNUMBER
16. AUDIT $ VARCHAR2 (38)
17. DEFROLENOT NULLNUMBER
18. DEFGRP # NUMBER
19. DEFGRP_SEQ # NUMBER
20. ASTATUSNOT NULLNUMBER
21. LCOUNTNOT NULLNUMBER
22. DEFSCHCLASS VARCHAR2 (30)
23. EXT_USERNAME VARCHAR2 (4000)
24. SPARE1 NUMBER
25. SPARE2 NUMBER
26. SPARE3 NUMBER
27. SPARE4 VARCHAR2 (1000)
28. SPARE5 VARCHAR2 (1000)
29. SPARE6DATE
31. SQL>Select User#,Name From User$Where Name='Scott';-- Find the user to change
33. USER#NAME
34. ----------------------------------------
35. 84 SCOTT
37. SQL>Select Count(*)FromScott. emp;
39. COUNT(*)
40. ----------
41. 14
43. SQL>Update User$Set Name='Scott2' Where User# = 84;-- Change User Name
45. 1 row updated.
47. SQL>Commit;-- Submit www.bkjia.com
48. CommitComplete.
51. SQL>Select Count(*)FromScott. emp;
53. COUNT(*)
54. ----------
55. 14
57. SQL>AlterSystemCheckpoint;
58. System altered.
60. SQL>Select Count(*)FromScott. emp;
62. COUNT(*)
63. ----------
64. 14
66. SQL>AlterSystem flush shared_pool;-- Refresh shared_pool
67. System altered.
69. SQL>Select Count(*)FromScott. emp;
70. Select Count(*)FromScott. emp
71. *
72. ERRORAtLine 1:
73. ORA-00942:Table Or ViewDoesNotExist
76. SQL>Select Count(*)FromScott2.emp;-- Changed successfully
78. COUNT(*)
79. ----------
80. 14