数据库版本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
测试结束!