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


posted @   lfree  阅读(454)  评论(0编辑  收藏  举报
编辑推荐:
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示