Oracle用户名更改操作四步走

Oracle用户名更改操作四步走 Oracle用户名修改是个不太常见的需求，但对很对入门者而言还是一个需要掌握的知识，这里通过四个简单步骤实现Oracle用户名的更改和替换。 AD：2013云计算架构师峰会课程资料下载 我们的Oracle管理工作中经常涉及到更改Oracle用户

Oracle用户名更改操作四步走

Oracle用户名修改是个不太常见的需求，但对很对入门者而言还是一个需要掌握的知识，这里通过四个简单步骤实现Oracle用户名的更改和替换。

AD：2013云计算架构师峰会课程资料下载

我们的Oracle管理工作中经常涉及到更改Oracle用户属性、密码之类的常用操作；但在某些应用场景下，会遇到Oracle用户名更改的需求，如何解决？下面通过四个步骤实现Oracle用户名的修改。

一、查询更改Oracle用户名

<ol>
<li><span><span>SQL> </span><span>select</span><span> </span><span>user</span><span>#,</span><span>name</span><span>,</span><span>password</span><span> </span><span>from</span><span> </span><span>user</span><span>$ </span><span>where</span><span> </span><span>name</span><span> =</span><span>'TICKETS'</span><span>;  </span></span></li>
<li><span> </span></li>
<li>
<span>     </span><span>USER</span><span># </span><span>NAME</span><span>                           </span><span>PASSWORD</span><span> </span>
</li>
<li>
<span>---------- ------------------------------ ------------------------------ </span><span> </span>
</li>
<li><span>        78 TICKETS                        21EDA78F89D6FACD </span></li>
</ol>

二、更改用户名

<ol>
<li><span><span>SQL> </span><span>update</span><span>  </span><span>user</span><span>$ </span><span>set</span><span> </span><span>name</span><span>=</span><span>'TICKETS_BAK'</span><span> </span><span>where</span><span> </span><span>user</span><span>#=78;  </span></span></li>
<li><span> </span></li>
<li><span>1 row updated.  </span></li>
<li><span> </span></li>
<li>
<span>SQL> </span><span>commit</span><span>;  </span>
</li>
<li><span> </span></li>
<li>
<span>Commit</span><span> complete. </span>
</li>
</ol>

三、创建同样的Oracle用户名

<ol>
<li><span><span>SQL> </span><span>create</span><span> </span><span>user</span><span> tickets identified </span><span>by</span><span> </span><span>"123456"</span><span> </span></span></li>
<li>
<span>  2  </span><span>default</span><span> tablespace yytickets  </span>
</li>
<li>
<span>  3  </span><span>temporary</span><span> tablespace </span><span>temp</span><span>;  </span>
</li>
<li>
<span>create</span><span> </span><span>user</span><span> tickets identified </span><span>by</span><span> </span><span>"123456"</span><span> </span>
</li>
<li><span>            *  </span></li>
<li>
<span>ERROR </span><span>at</span><span> line 1:  </span>
</li>
<li>
<span>ORA-01920: </span><span>user</span><span> </span><span>name</span><span> </span><span>'TICKETS'</span><span> conflicts </span><span>with</span><span> another </span><span>user</span><span> </span><span>or</span><span> role </span><span>name</span><span> </span>
</li>
</ol>

这时系统会提示“用户名冲突”，运行下面的SQL语句

<ol>
<li>
<span><span>SQL> </span><span>alter</span><span> system </span><span>checkpoint</span><span>;    </span><span><span>----强制写入数据文件 </span><span> ,即让在  二、更改用户名  这一个步骤中修改的</span></span></span><span><span>user</span><span>$</span>的结果（脏块）写入到数据文件上的<span><span>user</span><span>$</span></span>中</span><span></span>
</li>
<li><span> </span></li>
<li><span>System altered.  </span></li>
<li><span> </span></li>
<li>
<span>SQL> </span><span>alter</span><span> system flush shared_pool; <span> </span></span><span><span>----在下一步（四、创建相同的用户名）</span><span><span>create</span><span> </span><span>user</span><span> tickets时</span></span><span>，oracle</span>系统会自动修改系统表user$。而这里</span><pre class="brush:php;toolbar:false"><span><span>清除缓存中的数据字典信息（具体指user$），会使</span><span>oracle</span>系统在修改系统表user$<span>前</span><span>强制读取在</span>数据文件上的<span><span>user</span><span>$</span></span>里的实际数据（即更改后的数据） <span> </span></span><span></span>

 

System altered. 

四、创建相同的用户名

<ol>
<li><span><span>SQL> </span><span>create</span><span> </span><span>user</span><span> tickets identified </span><span>by</span><span> </span><span>"123456"</span><span> </span></span></li>
<li>
<span>  2  </span><span>default</span><span> tablespace yytickets  </span>
</li>
<li>
<span>  3  </span><span>temporary</span><span> tablespace </span><span>temp</span><span>;  </span>
</li>
<li><span> </span></li>
<li>
<span>User</span><span> created.  </span>
</li>
<li><span> </span></li>
<li>
<span>SQL> </span><span>grant</span><span> </span><span>connect</span><span>,resource </span><span>to</span><span> tickets;  </span>
</li>
<li><span> </span></li>
<li>
<span>Grant</span><span> succeeded.  </span>
</li>
<li><span> </span></li>
<li>
<span>SQL> </span><span>commit</span><span>;  </span>
</li>
<li><span> </span></li>
<li>
<span>Commit</span><span> complete. </span>
</li>
</ol>

这样基本完成Oracle用户名的查找、更改、替换、复制等操作，需要注意的是用户名冲突的排查和解决，以防发生后患。

【编辑推荐】

1. Oracle用户名重建索引方法探究
2. 全面讲解Oracle查询用户表空间
3. 浅析Oracle用户权限表的管理方法
4. Oracle数据库备份与恢复特性浅谈
5. 使用Oracle外部表的五个限制