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模式可以修改用户名
建议继续学习:
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:MySQL数据库性能优化之硬件瓶颈分析
后一篇:PostgreSQL查询优化简介 >>
文章信息
- 作者:惜分飞 来源: 惜分飞
- 标签: 重命名
- 发布时间:2012-08-20 23:48:09
近3天十大热文
- [66] Go Reflect 性能
- [66] Oracle MTS模式下 进程地址与会话信
- [65] 如何拿下简短的域名
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [59] android 开发入门
- [59] 图书馆的世界纪录
- [58] 【社会化设计】自我(self)部分――欢迎区
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 界面设计速成
- [47] 读书笔记-壹百度:百度十年千倍的29条法则