11.2.0.2里新增了一个新特性——用户重命名(Rename User)，在这个版本之前要想重命名用户，需要按用户导出，再fromuser touser（imp）或remap_schem（impdp）。还可以通过修改数据字典user$的方式来重命名用户，但这种方法可能会带来风险。有了这个特性之后，可以通过alter user oldname rename to newname identified by "password";一条命令来重命名用户。
下面来看测试过程，使用Oracle版本11.2.0.4
|
1
2
3
4
5
6
7
8
9
|
sys@ORCL>
select
* from
v
$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL
/SQL
Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS
for
Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
|
1、创建测试用户及测试表
|
1
2
3
4
5
6
7
8
9
10
11
|
sys@ORCL>create user sq identified by
"sq"
;
User created.
sys@ORCL>grant connect,resource to sq;
Grant succeeded
sys@ORCL>create table sq.t1 as
select
* from dba_tables;
Table created.
|
2、执行alter user语句
|
1
2
3
4
5
|
sys@ORCL>alter user sq rename to zlx identified by
"zlx"
;
alter user sq rename to zlx identified by
"zlx"
*
ERROR at line 1:
ORA-00922: missing or invalid option
|
报错无效的操作，原来是需要修改隐含参数"_enable_rename_user"
3、修改隐含参数"_enable_rename_user"，并以restrict模式重启数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
sys@ORCL>
alter
system
set
"_enable_rename_user"
=
true
scope=spfile;
System altered.
sys@ORCL>startup
restrict
force
;
ORACLE instance started.
Total System
Global
Area 1620115456 bytes
Fixed
Size
2253704 bytes
Variable
Size
956304504 bytes
Database
Buffers 654311424 bytes
Redo Buffers 7245824 bytes
Database
mounted.
Database
opened.
|
4、再次执行alter user语句，重命名用户
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
sys@ORCL>
alter
user
sq rename
to
zlx identified
by
"zlx"
;
User
altered.
sys@ORCL>
select
count
(*)
from
sq.t1;
select
count
(*)
from
sq.t1
*
ERROR
at
line 1:
ORA-00942:
table
or
view
does
not
exist
sys@ORCL>
select
count
(*)
from
zlx.t1;
COUNT
(*)
----------
2864
sys@ORCL>conn zlx/zlx
ERROR:
ORA-01035: ORACLE
only
available
to
users
with
RESTRICTED SESSION privilege
Warning: You are
no
longer connected
to
ORACLE.
sys@ORCL>conn /
as
sysdba
Connected.
sys@ORCL>
alter
system disable restricted session;
System altered.
sys@ORCL>conn zlx/zlx
Connected.
zlx@ORCL>
select
*
from
tab;
TNAME TABTYPE CLUSTERID
------------------------------------------ --------------------- ----------
T1
TABLE
|
从上面的结果来看用户名重命名成功。
5、reset隐含参数"_enable_rename_user"并重启数据库
sys@ORCL>alter system reset "_enable_rename_user";
System altered.
下面来看直接修改数据字典user$的方法：
sys@ORCL>select user#,name from user$ where name='ZLX';
USER# NAME
---------- --------
94 ZLX
sys@ORCL>update user$ set name='SQ' WHERE USER#=94;
1 row updated.
sys@ORCL>commit;
Commit complete.
sys@ORCL>conn sq/zlx;
Connected.
网上还提到update完后，需要alter system checkpoint;和alter system flush shared_pool;，从上面的操作来看好象更简单一些，但user$作为oracle的核心基表之一，修改数据字典会不会造成系统不稳定产生ora-600错误等等都不好说，所以生产上一定要慎重。
参考：http://www.linuxidc.com/Linux/2014-04/100166.htm
http://ylw6006.blog.51cto.com/470441/799261/
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1944765，如需转载请自行联系原作者
