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