11g修改用户名

数据库版本oracle 11203

修改用户名是11g的新功能，测试一下：

隐藏参数_enable_rename_user需要打开才能修改用户名，先查看一下默认的值。默认是false

SQL> SELECT   ksppinm, ksppstvl, ksppdesc
  2  FROM   x$ksppi x, x$ksppcv y
  3  WHERE x.indx = y.indx AND  ksppinm = '_enable_rename_user';

KSPPINM                   KSPPSTVL        KSPPDESC
------------------------- --------------- --------------------------------------------------
_enable_rename_user       FALSE           enable RENAME-clause using ALTER USER statement

 建立一个测试用户yss，目标是将yss改成yys

SQL> grant connect,resource to yss identified by yss;

Grant succeeded.

SQL> alter system set "_enable_rename_user" = true scope=memory;
alter system set "_enable_rename_user" = true scope=memory
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

隐藏参数_enable_rename_user需要重启instance后才能修改，先在spfile中修改再重新启动。

SQL> alter system set "_enable_rename_user" = true scope=memory;
alter system set "_enable_rename_user" = true scope=memory
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this 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
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             167772440 bytes
Database Buffers          138412032 bytes
Redo Buffers                4747264 bytes
Database mounted.
Database opened.

开始修改用户名时出错，按文档需要在startup restrict模式下才能修改。

SQL> alter user yss rename to yys identified by yys;
alter user yss rename to yys identified by yys
               *
ERROR at line 1:
ORA-00922: missing or invalid option

再启后继续测试：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             167772440 bytes
Database Buffers          138412032 bytes
Redo Buffers                4747264 bytes
Database mounted.
Database opened.
SQL>  alter user yss rename to yys identified by yys;

User altered.

更改成功

SQL> conn yss/yss
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn yys/yys
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

恢复隐藏参数为false，正常打开数据库：

SQL> alter system set "_enable_rename_user" = false  scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             167772440 bytes
Database Buffers          138412032 bytes
Redo Buffers                4747264 bytes
Database mounted.
Database opened.

SQL> conn yys/yys
Connected.
SQL> select * from dual;

D
-
X

测试结束！