下面来看测试过程，使用Oracle版本11.2.0.4
sys@ORCL>select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
1、创建测试用户及测试表
sys@ORCL>create user sq identified by "sq";
User created.
sys@ORCL>grant connect,resource to sq;
Grant succeeded
sys@ORCL>create table sq.t1 as select * from dba_tables;
Table created.
2、执行alter user语句
sys@ORCL>alter user sq rename to zlx identified by "zlx";
alter user sq rename to zlx identified by "zlx"
*
ERROR at line 1:
ORA-00922: missing or invalid option
报错无效的操作，原来是需要修改隐含参数"_enable_rename_user"
3、修改隐含参数"_enable_rename_user"，并以restrict模式重启数据库
sys@ORCL>alter system set "_enable_rename_user"=true scope=spfile;
System altered.
sys@ORCL>startup restrict force;
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 956304504 bytes
Database Buffers 654311424 bytes
Redo Buffers 7245824 bytes
Database mounted.
Database opened.
4、再次执行alter user语句，重命名用户
sys@ORCL>alter user sq rename to zlx identified by "zlx";
User altered.
sys@ORCL>select count(*) from sq.t1;
select count(*) from sq.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
sys@ORCL>select count(*) from zlx.t1;
COUNT(*)
----------
2864
sys@ORCL>conn zlx/zlx
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Warning: You are no longer connected to ORACLE.
sys@ORCL>conn / as sysdba
Connected.
sys@ORCL>alter system disable restricted session;
System altered.
sys@ORCL>conn zlx/zlx
Connected.
zlx@ORCL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------ --------------------- ----------
T1 TABLE