Oracle 11g修改数据库用户名

Oracle 11g修改数据库用户名

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

1.[oracle@node1 ~]$ sqlplus sys/Ab123456@rac5 as sysdba

2.SQL*Plus: Release 11.2.0.

3.0 Production on Tue Mar 6 19:42:12 2012

3.Copyright (c) 1982, 2011, Oracle. All rights reserved.

4.

5.Connected to:

6.Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc

tion

7.With the Partitioning, Real Application Clusters, Automatic Storage Mana

gement, OLAP,

8.Data Mining and Real Application Testing options

9.

10.SQL> create user xxx identified by 123456 default tablespace users;

https://www.360docs.net/doc/0716394143.html,er created.

12.

13.SQL> grant resource,connect to xxx;

14.Grant succeeded.

15.

16.SQL> create table xxx.test as select * from dba_objects;

17.Table created.

18.

19.SQL> alter user xxx rename to yyy;

20.alter user xxx rename to yyy

21. *

22.ERROR at line 1:

23.ORA-00922: missing or invalid option

24.

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

26.alter user xxx rename to yyy identified by 123456

27. *

28.ERROR at line 1:

29.ORA-00922: missing or invalid option

30.

31.SQL> create pfile from spfile;

32.File created.

33.

34.[oracle@node1 ~]$ srvctl stop database -d rac -o immediate

35.[oracle@node1 ~]$ srvctl status database -d rac

36.Instance node1 is not running on node node1

37.Instance node2 is not running on node node2

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

1.[oracle@node1 ~]$ cd /u01/app/oracle/product/11.

2.0/db1/dbs/

2.[oracle@node1 dbs]$ ls

3.hc_node1.dat initnode1.ora init.ora orapwnode1

4.

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

6.*._enable_rename_user='TRUE'

7.

8.[oracle@node1 dbs]$ sqlplus /nolog

9.SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 19:51:41 2012

10.Copyright (c) 1982, 2011, Oracle. All rights reserved.

11.

12.SQL> conn /as sysdba

13.Connected to an idle instance.

14.SQL> startup restrict pfile=$ORACLE_HOME/dbs/initnode1.ora

15.ORACLE instance started.

16.

17.Total System Global Area 1235959808 bytes

18.Fixed Size 2227904 bytes

19.Variable Size 805306688 bytes

20.Database Buffers 419430400 bytes

21.Redo Buffers 8994816 bytes

22.Database mounted.

23.Database opened.

24.SQL> select open_mode,name from v$database;

25.

26.OPEN_MODE NAME

27.-------------------- ---------------

28.READ WRITE RAC

29.

30.SQL> show parameter spfile;

31.

https://www.360docs.net/doc/0716394143.html, TYPE VALUE

33.------------------------------------ ----------- -----------------------

-------

34.spfile string

三：修改xxx用户名为yyy

1.SQL> alter user xxx rename to yyy;

2.alter user xxx rename to yyy

3. *

4.ERROR at line 1:

5.ORA-02000: missing IDENTIFIED keyword

6.

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

https://www.360docs.net/doc/0716394143.html,er altered.

9.

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

11.

12. COUNT(*)

13.----------

14. 74556

四：使用spfile启动rac

1.SQL> shutdown immediate;

2.Database closed.

3.Database dismounted.

4.ORACLE instance shut down.

5.

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

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

8.Instance node1 is running on node node1

9.Instance node2 is running on node node2

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

1.SQL*Plus: Release 11.

2.0.

3.0 Production on Tue Mar 6 20:00:52 2012

2.Copyright (c) 1982, 2011, Oracle. All rights reserved.

3.

4.Connected to:

5.Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc

tion

6.With the Partitioning, Real Application Clusters, Automatic Storage Mana

gement, OLAP,

7.Data Mining and Real Application Testing options

8.

9.SQL> select * from tab;

10.

11.TNAME TABTYPE CLUSTERID

12.------------------------------ ------- ----------

13.TEST TABLE

14.

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

16.SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 20:01:22 2012

17.Copyright (c) 1982, 2011, Oracle. All rights reserved.

18.

19.ERROR:

20.ORA-01017: invalid username/password; logon denied