ORACLE用户重命名

发表于 2012 年 8 月 20 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：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('%&param%')
  7  order by name
  8  /
Enter value for param: _enable_rename_user
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
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模式可以修改用户名

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

《ORACLE用户重命名》有 2 条评论

  1. 孙奇 说：
    2012 年 9 月 10 日11:21

    要是代码上含有注释就好了，小弟刚入门看的有点费劲

  2. 惜分飞 说：
    2012 年 8 月 20 日15:00

    11.2.0.2重命名新功能实现原理

    =====================
PARSING IN CURSOR #3041965520 len=32 dep=1 uid=0 oct=7 lid=0 tim=1323806332068299 hv=2990570705 ad='2d5b38c4' sqlid='8tp9fa2t40z6j'
delete from user$ where user#=:1
END OF STMT
PARSE #3041965520:c=1000,e=1113,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1323806332068297
BINDS #3041965520:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551c280  bln=22  avl=02  flg=05
  value=85
WAIT #3041965520: nam='db file sequential read' ela= 65 file#=3 block#=8419 blocks=1 obj#=0 tim=1323806332071651
EXEC #3041965520:c=4000,e=3682,p=1,cr=1,cu=7,mis=1,r=1,dep=1,og=4,plh=3816956542,tim=1323806332072277
STAT #3041965520 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  USER$ (cr=1 pr=1 pw=0 time=930 us)'
STAT #3041965520 id=2 cnt=1 pid=1 pos=1 obj=47 op='INDEX RANGE SCAN I_USER2 (cr=1 pr=0 pw=0 time=41 us cost=1 size=37 card=1)'
CLOSE #3041965520:c=0,e=14,dep=1,type=0,tim=1323806332072647
=====================
PARSING IN CURSOR #3041965520 len=438 dep=1 uid=0 oct=2 lid=0 tim=1323806332074048 hv=1755424277 ad='2d533948' sqlid='2trzmq5na39hp'
insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,defrole,resource$,ltime,exptime,astatus,lcount,defschclass,
spare1,spare4,ext_username,spare2)values (:1,:2,:3,SYSDATE,DECODE(to_char(:4, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :4),
:5,:6,:7,:8,:9,DECODE(to_char(:10, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :10),DECODE(to_char(:11, 'YYYY-MM-DD'), '0000-00-00', 
to_date(NULL), :11),:12,:13,:14,:15,:16,:17,:18)
END OF STMT
PARSE #3041965520:c=1000,e=1325,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1323806332074045
BINDS #3041965520:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551be74  bln=22  avl=02  flg=05
  value=85
 Bind#1
  oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=2d50086e  bln=32  avl=03  flg=09
  value="XFF"
 Bind#2
  oacdty=01 mxl=32(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=2d50088e  bln=32  avl=16  flg=09
  value="72829DD49D510C88"
 Bind#3
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=2d5008e1  bln=08  avl=07  flg=09
  value="12/14/2011 3:54:26"
 Bind#4
  No oacdef for this bind.
 Bind#5
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551be50  bln=24  avl=02  flg=05
  value=4
 Bind#6
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551be2c  bln=24  avl=02  flg=05
  value=3
 Bind#7
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551be08  bln=24  avl=02  flg=05
  value=1
 Bind#8
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551bde4  bln=24  avl=02  flg=05
  value=1
 Bind#9
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551bdc0  bln=24  avl=01  flg=05
  value=0
 Bind#10
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=2d5008ef  bln=08  avl=07  flg=09
  value="0/0/-10100 -1:-1:-1"
 Bind#11
  No oacdef for this bind.
 Bind#12
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=2d5008e8  bln=08  avl=07  flg=09
  value="0/0/-10100 -1:-1:-1"
 Bind#13
  No oacdef for this bind.
 Bind#14
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551bd9c  bln=24  avl=01  flg=05
  value=0
 Bind#15
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551bd78  bln=24  avl=01  flg=05
  value=0
 Bind#16
  oacdty=01 mxl=32(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=2d5008ae  bln=32  avl=22  flg=09
  value="DEFAULT_CONSUMER_GROUP"
 Bind#17
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551bd54  bln=24  avl=01  flg=05
  value=0
 Bind#18
  oacdty=01 mxl=128(62) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=128 off=0
  kxsbbbfp=2d500902  bln=128  avl=62  flg=09
  value="S:4CB6F7F259A3E37046AEA521C6F644DAE4C3EE6136FFB7B512CB71B1DFD4"
 Bind#19
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=00000000  bln=32  avl=00  flg=09
 Bind#20
  oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b551bd30  bln=24  avl=00  flg=05
EXEC #3041965520:c=7000,e=6819,p=0,cr=1,cu=5,mis=1,r=1,dep=1,og=4,plh=0,tim=1323806332081153
STAT #3041965520 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=461 us)'
CLOSE #3041965520:c=0,e=13,dep=1,type=0,tim=1323806332081334



PARSING IN CURSOR #3041965520 len=3509 dep=1 uid=57 oct=47 lid=57 tim=1323806332169524 hv=1920321438 ad='2d60623c' sqlid='38spc81t7bjwy'
declare
 TYPE attrs_cur IS REF CURSOR;
 m_cur       attrs_cur;
 m_event   varchar2(512);
 m_user    varchar2(512);
 m_owner   varchar2(512);
 m_user1   varchar2(512);
 m_type    varchar2(512);
 m_name    varchar2(5120);
 m_column  varchar2(5120);
 m_cnt     NUMBER;
 m_stmt    varchar2(512);
 m_ret     varchar2(3000);
 m_ret1     varchar2(512);
 m_o_stmt VARCHAR2(5120);
begin
 m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
 execute immediate m_stmt into m_type;
 if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER' or m_type='TABLESPACE'))
 then
   return;
 end if;
 m_stmt:='select sys.dbms_standard.sysevent from dual';
 execute immediate m_stmt into m_event;
 m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';
 execute immediate m_stmt into m_user;
 m_stmt:='select sys.dbms_standard.login_user  from dual';
 execute immediate m_stmt into m_user1;
 m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
 execute immediate m_stmt into m_owner;
 m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
 execute immediate m_stmt into m_name;
 if((instr(upper(m_name),'MDRT_')>0) and m_event='DROP')
 then
   return;
 end if;
 if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='CREATE')
 then
   m_stmt:='select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2) from dual';
   execute immediate m_stmt  into m_ret  using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name);
   m_ret:=trim(m_ret);
   while (length(m_ret)!=0) loop
     if (instr(m_ret,' $$__## ')!=0)
     then
       m_ret1:=trim(substr(m_ret,1,instr(m_ret,' $$__## ')-1));
       m_ret:=trim(substr(m_ret,instr(m_ret,' $$__## ')+8));
     else
       m_ret1:=trim(m_ret);
       m_ret:='';
     end if;
     m_stmt:='begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;';
    execute immediate m_stmt using m_owner||'.'||m_name,SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME(m_ret1);
   end loop;
   return;
 end if;
  if (m_name!='MDSYS' and m_type='USER' and m_event='DROP')
  then
     m_stmt:='call sdo_geor_def.doDropUserAndTable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='DROP')
  then
     m_stmt:='call sdo_geor_def.doDropUserAndTable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='TRUNCATE')
  then
     m_stmt:='call sdo_geor_def.doTruncateTable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
  then
     m_stmt:='call sdo_geor_def.doAlterRenameTable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='RENAME')
  then
     m_stmt:='call sdo_geor_def.doRenameTable()';
     execute immediate m_stmt;
  end if;
 if (m_event='DROP' and m_type='TABLE')
 then
   m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
   EXECUTE IMMEDIATE m_stmt;
 end if;
 if ((m_type='USER' and m_event='DROP') or (m_type='TABLESPACE' and m_event='DROP'))
 then
   m_stmt:='delete from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt;
 end if;
 Exception
   when others then
     if(sqlcode=-13391)
     then
       m_stmt:=sqlerrm;
       m_stmt:=substr(m_stmt,11);
       m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391,'''||m_stmt||''')';
       execute immediate m_stmt;
     end if;
end;

    通过10046生成的trace文件分析,该功能主要是通过drop user+create user+modify object dict来实现该功能