[20170628]11g批改用户名.txt

[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模式下完成操作.