本文来自我的github pages博客http://galengao.github.io/ 即www.gaohuirong.cn
[转自]http://www.xifenfei.com/2012/08/oracle%e7%94%a8%e6%88%b7%e9%87%8d%e5%91%bd%e5%90%8d.html
从oracle 11.2.0.2开始提供了用户重命名的新特性,在以前的版本中,如果想对用户重命名,一般来说是先创建一个新的用户并授权,然后将原用户下的所有对象导入,然后删除旧的用户!
数据库版本信息
SQL> select* from v$version;BANNER
--------------------------------------------------------------------------------
Oracle Database11g Enterprise Edition Release 11.2.0.3. - Production
PL/SQL Release 11.2.0.3. - Production
CORE11.2.0.3.0 Production
TNS forLinux: Version 11.2.0.3. - Production
NLSRTL Version 11.2.0.3. - Production
创建测试环境
SQL> create user xifenfei identified by xifenfei;User created.SQL> grant connect,resource to xifenfei;Grant succeeded.SQL> conn xifenfei/xifenfei
Connected.SQL> create table t_xifenfei as select* from user_users;Table created.SQL> create index ind_t_xifenfei on t_xifenfei(user_id);Index created.SQL> conn / assysdba
Connected.
SQL> select object_type,object_name from dba_objects where owner='XIFENFEI';OBJECT_TYPE OBJECT_NAME
------------------- ---------------------------------------------------------
TABLE T_XIFENFEI
INDEX IND_T_XIFENFEI
尝试修改用户名
SQL> alter user xifenfei rename to xff identified by xifenfei;
alter user xifenfei rename to xff identified by xifenfei
*
ERROR atline :
ORA-: missing orinvalid option--默认值是false
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.inst_id = USERENV ('Instance')
and b.inst_id = USERENV ('Instance')
and a.indx = b.indx
and upper(a.ksppinm) LIKE upper('%¶m%')
order by name
/
Enter value forparam: _enable_rename_user
old :andupper(a.ksppinm) LIKEupper('%¶m%')
new :andupper(a.ksppinm) LIKEupper('%_enable_rename_user%')NAME VALUEDESCRIPTION
-------------------------------- ------------------------ ------------------------------------------------
_enable_rename_user FALSEenable RENAME-clause using ALTERUSERstatementSQL> startup force restrict
ORACLE instance started.Total System GlobalArea bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers6516736 bytes
Database mounted.
Database opened.--_enable_rename_user=false,在restrict模式也不能修改用户名
SQL> ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei;
ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei
*
ERROR atline :
ORA-: missing orinvalid option
设置隐含参数
SQL> alter system set "_enable_rename_user"=true scope=spfile;System altered.SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup restrict
ORACLE instance started.Total System GlobalArea bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers6516736 bytes
Database mounted.
Database opened.
SQL> ALTER user xifenfei RENAME TO xff IDENTIFIED BY xifenfei;Useraltered.
测试结果
SQL> startup force
ORACLE instance started.Total System GlobalArea bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers6516736 bytes
Database mounted.
Database opened.
SQL> select object_type,object_name from dba_objects where owner='XIFENFEI';no rows selectedSQL> select object_type,object_name from dba_objects where owner='XFF';OBJECT_TYPE OBJECT_NAME
------------------- ----------------------------------------------------
TABLE T_XIFENFEI
INDEX IND_T_XIFENFEISQL> conn xff/xifenfei
Connected.
SQL> select count(*) from t_xifenfei; COUNT(*)
----------
相关文档和上面的测试,得出结论:数据库版本在11.2.0.2及其以上版本,_enable_rename_user设置为true,数据库启动到restrict模式可以修改用户名