Oracle 11gR2 用户重命名(rename user)

11.2.0.2里新增了一个新特性--用户重命名(Rename User)，在这个版本之前要想重命名用户，需要按用户导出，再fromuser touser（imp）或remap_schem（impdp）。还可以通过修改数据字典user$的方式来重命名用户，但这种方法可能会带来风险。有了这个特性之后，可以通过alter user oldname rename to newname identified by "password";一条命令来重命名用户。

下面来看测试过程，使用Oracle版本11.2.0.4

sys@ORCL>select * from v$version;BANNER-------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0      ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

1、创建测试用户及测试表

sys@ORCL>create user sq identified by "sq";User created.sys@ORCL>grant connect,resource to sq; Grant succeededsys@ORCL>create table sq.t1 as select * from dba_tables; Table created.

2、执行alter user语句

sys@ORCL>alter user sq rename to zlx identified by "zlx";alter user sq rename to zlx identified by "zlx"              *ERROR at line 1:ORA-00922: missing or invalid option

报错无效的操作，原来是需要修改隐含参数"_enable_rename_user"

3、修改隐含参数"_enable_rename_user"，并以restrict模式重启数据库

sys@ORCL>alter system set "_enable_rename_user"=true scope=spfile; System altered. sys@ORCL>startup restrict force;ORACLE instance started. Total System Global Area 1620115456 bytesFixed Size                    2253704 bytesVariable Size                  956304504 bytesDatabase Buffers          654311424 bytesRedo Buffers                    7245824 bytesDatabase mounted.Database opened.

4、再次执行alter user语句，重命名用户

sys@ORCL>alter user sq rename to zlx identified by "zlx"; User altered.sys@ORCL>select count(*) from sq.t1;select count(*) from sq.t1                        *ERROR at line 1:ORA-00942: table or view does not exist  sys@ORCL>select count(*) from zlx.t1;   COUNT(*)----------      2864 sys@ORCL>conn zlx/zlxERROR:ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege  Warning: You are no longer connected to ORACLE.sys@ORCL>conn / as sysdbaConnected.sys@ORCL>alter system disable restricted session; System altered. sys@ORCL>conn zlx/zlxConnected.zlx@ORCL>select * from tab; TNAME                                           TABTYPE                  CLUSTERID------------------------------------------ --------------------- ----------T1                                               TABLE

从上面的结果来看用户名重命名成功。

5、reset隐含参数"_enable_rename_user"并重启数据库

sys@ORCL>alter system reset "_enable_rename_user";

System altered.

下面来看直接修改数据字典user$的方法：

sys@ORCL>select user#,name from user$ where name='ZLX';

USER# NAME

---------- --------

94 ZLX

sys@ORCL>update user$ set name='SQ' WHERE USER#=94;

1 row updated.

sys@ORCL>commit;

Commit complete.

sys@ORCL>conn sq/zlx;

Connected.

网上还提到update完后，需要alter system checkpoint;和alter system flush shared_pool;，从上面的操作来看好象更简单一些，但user$作为oracle的核心基表之一，修改数据字典会不会造成系统不稳定产生ora-600错误等等都不好说，所以生产上一定要慎重。

参考：http://www.linuxidc.com/Linux/2014-04/100166.htm

http://ylw6006.blog.51cto.com/470441/799261/