[20170628]11g修改用戶名.txt--//昨天看了鏈接,提到修改用戶名:http://www.oratea.com/2017/06/26/oracle-11g%e4%bf%ae%e6%94%b9%e7%94%a8%e6%88%b7%e5%90%8d/--//自己也測試看看.1.環境:SCO ...
[20170628]11g修改用戶名.txt
--//昨天看了鏈接,提到修改用戶名:
http://www.oratea.com/2017/06/26/oracle-11g%e4%bf%ae%e6%94%b9%e7%94%a8%e6%88%b7%e5%90%8d/
--//自己也測試看看.
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> grant dba to sss IDENTIFIED BY sss;
Grant succeeded.
SYS@book> @ &r/hide _enable_rename_user
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------- ----------------------------------------------- ------------- ------------- ------------
_enable_rename_user enable RENAME-clause using ALTER USER statement TRUE FALSE FALSE
--//預設值是false.
--//從Oracle 11g開始，修改用戶名就比較方便了，直接如下：
SYS@book> alter system set "_enable_rename_user" = true scope=memory;
alter system set "_enable_rename_user" = true scope=memory
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
--//必須修改spfile,重啟才生效.先嘗試不修改參數的情況.
SYS@book> alter user sss rename to ttt identified by ttt;
alter user sss rename to ttt identified by ttt
*
ERROR at line 1:
ORA-00922: missing or invalid option
2.修改參數重啟再測試:
SYS@book> alter system set "_enable_rename_user" = true scope=spfile ;
System altered.
--//重啟庫，考慮到重啟庫後應用直接連進來，可以使用 restrict重啟實例.
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup restrict
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SYS@book> alter user sss rename to ttt identified by ttt;
User altered.
--//OK修改成功.
3.再次重啟,修改回來看看.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
--//打開另外會話使用ttt用戶登錄:
TTT@book> select * from dual ;
D
-
X
SYS@book> alter user ttt rename to sss identified by sss;
alter user ttt rename to sss identified by sss
*
ERROR at line 1:
ORA-00922: missing or invalid option
--//可以發現有ttt用登錄是無法修改的.退出以上會話再測試看看!!
SYS@book> alter system flush shared_pool ;
System altered.
SYS@book> alter user ttt rename to sss identified by sss;
alter user ttt rename to sss identified by sss
*
ERROR at line 1:
ORA-00922: missing or invalid option
--//實際上只能在restrict模式下修改:
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup restrict
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SYS@book> alter user ttt rename to sss identified by sss;
User altered.
4.還原:
SYS@book> alter system reset "_enable_rename_user";
System altered.
5.總結:
--//方便談不上,只不過提供一種方式修改用戶名.而且必須重啟資料庫在restrict模式下完成操作.
