ORACLE用户重命名

　　从oracle 11.2.0.2开始提供了用户重命名的新特性，在以前的版本中，如果想对用户重命名，一般来说是先创建一个新的用户并授权，然后将原用户下的所有对象导入，然后删除旧的用户!

　　数据库版本信息

　　SQL> select * from v$version;

　　BANNER

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

　　Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

　　PL/SQL Release 11.2.0.3.0 - Production

　　CORE 11.2.0.3.0 Production

　　TNS for Linux: 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 / as sysdba

　　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 at line 1:

　　ORA-00922: missing or invalid 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

　　2 from x$ksppi a,x$ksppcv b

　　3 where a.inst_id = USERENV (\'Instance\')

　　and b.inst_id = USERENV (\'Instance\')

　　4 5 and a.indx = b.indx

　　6 and upper(a.ksppinm) LIKE upper(\'%¶m%\')

　　7 order by name

　　8 /

　　Enter value for param: _enable_rename_user

　　old 6: and upper(a.ksppinm) LIKE upper(\'%¶m%\')

　　new 6: and upper(a.ksppinm) LIKE upper(\'%_enable_rename_user%\')

　　NAME VALUE DESCRIPTION

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

　　_enable_rename_user FALSE enable RENAME-clause using ALTER USER statement

　　SQL> startup force restrict

　　ORACLE instance started.

　　Total System Global Area 230162432 bytes

　　Fixed Size 1344088 bytes

　　Variable Size 88083880 bytes

　　Database Buffers 134217728 bytes

　　Redo Buffers 6516736 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 at line 1:

　　ORA-00922: missing or invalid 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 Global Area 230162432 bytes

　　Fixed Size 1344088 bytes

　　Variable Size 88083880 bytes

　　Database Buffers 134217728 bytes

　　Redo Buffers 6516736 bytes

　　Database mounted.

　　Database opened.

　　SQL> ALTER user xifenfei RENAME TO xff IDENTIFIED BY xifenfei;

　　User altered.

　　测试结果

　　SQL> startup force

　　ORACLE instance started.

　　Total System Global Area 230162432 bytes

　　Fixed Size 1344088 bytes

　　Variable Size 88083880 bytes

　　Database Buffers 134217728 bytes

　　Redo Buffers 6516736 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

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

原文转自：http://blogread.cn/it/article/5709