oracle 11.2.0.2以后对数据库用户名重命名

本文来自我的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.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE11.2.0.3.0  Production
TNS forLinux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - 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 1:
ORA-00922: missing orinvalid option
 
--默认值是false
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
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('%&param%')
  order by name
  /
Enter value forparam: _enable_rename_user
old   6:andupper(a.ksppinm) LIKEupper('%&param%')
new   6:andupper(a.ksppinm) LIKEupper('%_enable_rename_user%')
 
NAME VALUEDESCRIPTION
-------------------------------- ------------------------ ------------------------------------------------
_enable_rename_user  FALSEenable RENAME-clause using ALTERUSERstatement
 
SQL> startup force restrict
ORACLE instance started.
 
Total System GlobalArea  230162432 bytes
Fixed Size  1344088 bytes
Variable Size  88083880 bytes
Database Buffers  134217728 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 1:
ORA-00922: 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  230162432 bytes
Fixed Size  1344088 bytes
Variable Size  88083880 bytes
Database Buffers  134217728 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  230162432 bytes
Fixed Size  1344088 bytes
Variable Size  88083880 bytes
Database Buffers  134217728 bytes
Redo Buffers6516736 bytes
Database mounted.
Database opened.
SQL> select object_type,object_name from dba_objects where owner='XIFENFEI';
 
no rows selected
 
SQL> select object_type,object_name from dba_objects where owner='XFF';
 
OBJECT_TYPE OBJECT_NAME
------------------- ----------------------------------------------------
TABLE   T_XIFENFEI
INDEX   IND_T_XIFENFEI
 
SQL> conn xff/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
 
  COUNT(*)
----------
 1

相关文档和上面的测试,得出结论:数据库版本在11.2.0.2及其以上版本,_enable_rename_user设置为true,数据库启动到restrict模式可以修改用户名