联系:手机/微信(+86 17813235971) QQ(107644445)
标题: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 ------------------- ---------------------------------------------------- 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模式可以修改用户名
要是代码上含有注释就好了,小弟刚入门看的有点费劲
11.2.0.2重命名新功能实现原理
通过10046生成的trace文件分析,该功能主要是通过drop user+create user+modify object dict来实现该功能