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. - 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('%&param%')

  order by name

  /

Enter value forparam: _enable_rename_user

old   :andupper(a.ksppinm) LIKEupper('%&param%')

new   :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   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 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(*)

----------

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

巴特西

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

最新文章

热门文章