技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> MySQL --> ORACLE用户重命名

ORACLE用户重命名

浏览:1621次  出处信息

    从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
------------------- ----------------------------------------------------
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模式可以修改用户名

建议继续学习:

  1. windows批量重命名文件名,自动修改大小写    (阅读:4276)
  2. 漫画:你能帮我把这个文件重命名吗?    (阅读:1991)
  3. 在 Linux 上复制和重命名文件    (阅读:756)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1