oracle11g数据库改用户名,Oracle 11g支持修改数据库用户名

最近翻看某blog，发现原来oracle 11g提供了用户重命名的新特性，在10g环境下，如果想对用户重命名，一般来说是先创建一个新的用户并授权，然后将原用户下的所有对象导入，然后删除旧的用户！下面来在11g rac环境下介绍下这个新特性！

一：创建一个测试用户xxx,并写入测试数据，由spfile文件生成pfile文件，关闭rac数据库

[oracle@node1 ~]$ sqlplus sys/Ab123456@rac5assysdba

SQL*Plus: Release 11.2.0.3.0 ProductiononTue Mar 6 19:42:12 2012

Copyright (c) 1982, 2011, Oracle.Allrights reserved.

Connectedto:

OracleDatabase11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Withthe Partitioning,RealApplication Clusters, Automatic Storage Management, OLAP,

Data MiningandRealApplication Testing options

SQL>createuserxxx identifiedby123456defaulttablespace users;

Usercreated.

SQL>grantresource,connecttoxxx;

Grantsucceeded.

SQL>createtablexxx.testasselect*fromdba_objects;

Tablecreated.

SQL>alteruserxxx renametoyyy;

alteruserxxx renametoyyy

*

ERRORatline 1:

ORA-00922: missingorinvalidoption

SQL>alteruserxxx renametoyyy identifiedby123456;

alteruserxxx renametoyyy identifiedby123456

*

ERRORatline 1:

ORA-00922: missingorinvalidoption

SQL>createpfilefromspfile;

File created.

[oracle@node1 ~]$ srvctl stopdatabase-d rac -o immediate

[oracle@node1 ~]$ srvctl statusdatabase-d rac

Instance node1isnotrunningonnode node1

Instance node2isnotrunningonnode node2

二：修改pfile文件，添加隐含参数 *._enable_rename_user='TRUE'，将数据库以restrict方式启动

[oracle@node1 ~]$ cd /u01/app/oracle/product/11.2.0/db1/dbs/

[oracle@node1 dbs]$ ls

hc_node1.dat  initnode1.ora  init.ora  orapwnode1

[oracle@node1 dbs]$ tail -1 initnode1.ora

*._enable_rename_user='TRUE'

[oracle@node1 dbs]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 ProductiononTue Mar 6 19:51:41 2012

Copyright (c) 1982, 2011, Oracle.Allrights reserved.

SQL> conn /assysdba

Connectedtoan idle instance.

SQL> startuprestrictpfile=$ORACLE_HOME/dbs/initnode1.ora

ORACLE instance started.

Total SystemGlobalArea 1235959808 bytes

FixedSize2227904 bytes

VariableSize805306688 bytes

DatabaseBuffers          419430400 bytes

Redo Buffers                8994816 bytes

Databasemounted.

Databaseopened.

SQL>selectopen_mode,namefromv$database;

OPEN_MODENAME

-------------------- ---------------

READWRITE           RAC

SQL> show parameter spfile;

NAMETYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string

三：修改xxx用户名为yyy

SQL>alter user xxx rename to yyy;

alter user xxx rename to yyy

*

ERROR at line 1:

ORA-02000: missing IDENTIFIED keyword

SQL>alter user xxx rename to yyy identified by 123456;

User altered.

SQL>select count(*) from yyy.test;

COUNT(*)

----------

74556

四：使用spfile启动rac

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@node1 dbs]$ srvctl start database -d rac

[oracle@node1 dbs]$ srvctl status database -d rac

Instance node1 is running on node node1

Instance node2 is running on node node2

五：连接测试，由此可见，对用户的rename操作，可以继承原有的权限

SQL*Plus: Release 11.2.0.3.0 ProductiononTue Mar 6 20:00:52 2012

Copyright (c) 1982, 2011, Oracle.Allrights reserved.

Connectedto:

OracleDatabase11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Withthe Partitioning,RealApplication Clusters, Automatic Storage Management, OLAP,

Data MiningandRealApplication Testing options

SQL>select*fromtab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TESTTABLE

[oracle@node1 ~]$ sqlplus xxx/123456@rac5

SQL*Plus: Release 11.2.0.3.0 ProductiononTue Mar 6 20:01:22 2012

Copyright (c) 1982, 2011, Oracle.Allrights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied