最近翻看某blog,发现原来oracle 11g提供了用户重命名的新特性,在10g环境下,如果想对用户重命名,一般来说是先创建一个新的用户并授权,然后将原用户下的所有对象导入,然后删除旧的用户!下面来在11g rac环境下介绍下这个新特性!
参考:http://www.orafan.net/blog/315.html,感谢作者分享!
一:创建一个测试用户xxx,并写入测试数据,由spfile文件生成pfile文件,关闭rac数据库
- [oracle@node1 ~]$ sqlplus sys/Ab123456@rac5 as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 19:42:12 2012
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> create user xxx identified by 123456 default tablespace users;
- User created.
- SQL> grant resource,connect to xxx;
- Grant succeeded.
- SQL> create table xxx.test as select * from dba_objects;
- Table created.
- SQL> alter user xxx rename to yyy;
- alter user xxx rename to yyy
- *
- ERROR at line 1:
- ORA-00922: missing or invalid option
- SQL> alter user xxx rename to yyy identified by 123456;
- alter user xxx rename to yyy identified by 123456
- *
- ERROR at line 1:
- ORA-00922: missing or invalid option
- SQL> create pfile from spfile;
- File created.
- [oracle@node1 ~]$ srvctl stop database -d rac -o immediate
- [oracle@node1 ~]$ srvctl status database -d rac
- Instance node1 is not running on node node1
- Instance node2 is not running on node 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 Production on Tue Mar 6 19:51:41 2012
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn /as sysdba
- Connected to an idle instance.
- SQL> startup restrict pfile=$ORACLE_HOME/dbs/initnode1.ora
- ORACLE instance started.
- Total System Global Area 1235959808 bytes
- Fixed Size 2227904 bytes
- Variable Size 805306688 bytes
- Database Buffers 419430400 bytes
- Redo Buffers 8994816 bytes
- Database mounted.
- Database opened.
- SQL> select open_mode,name from v$database;
- OPEN_MODE NAME
- -------------------- ---------------
- READ WRITE RAC
- SQL> show parameter spfile;
- NAME TYPE 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 Production on Tue Mar 6 20:00:52 2012
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ ------- ----------
- TEST TABLE
- [oracle@node1 ~]$ sqlplus xxx/123456@rac5
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 20:01:22 2012
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- ERROR:
- ORA-01017: invalid username/password; logon denied
本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/799261如需转载请自行联系原作者
ylw6006