步骤概述：
1. 停库，修改隐含参数_enable_rename_user 为true
2. 以 restrict方式启动数据库
3. alter user aaaa rename to bbbb identified by bbbb123;
4. 正常重启数据库
以下是测试步骤：
一、查看 隐含参数“_enable_rename_user” 默认是禁止
set linesize 321
column name format a30
column value format a25
col describ for a50
SQL>
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%rename%'
NAME VALUEDESCRIB
------------------------------ ------------------------- --------------------------------------------------
_enable_rename_user FALSEenable RENAME-clause using ALTER USER statement
创建测试user
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> conn / as sysdba
Connected.
SQL> create user yyf identified by yyf default tablespace yyf;
User created.
SQL> grant dba to yyf;
Grant succeeded.
SQL>
SQL> conn yyf/yyf;
Connected.
创建dblink 和物化视图并测试连通性，后面测试要用
SQL> create database link link_to_scott connect to scott identified by tiger using '119.179';
Database link created.
SQL> select * from dual@link_to_scott;
D
-
X
SQL> CREATE MATERIALIZED VIEW yyf.emp
REFRESH FORCE ON DEMAND
START WITH sysdate NEXT SYSDATE + 10/1442
AS
SELECT * FROM emp@link_to_scott;
Materialized view created.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL>
创建pfile，修改参数文件
SQL> conn / as sysdba
create pfile from spfile;
File created.
SQL>
[oracle@test1 dbs]$ tail -1 initgod.ora
*._enable_rename_user='TRUE'
以restrict 方式启动数据库，并进行rename操作。
SQL> startup restrict pfile=$ORACLE_HOME/dbs/initgod.ora
Database opened.
SQL> alter user yyf rename to yyf123 identified by yyf123;
User altered.
去掉隐含参数并重启数据库以新用户登入
SQL> startup
ORACLE instance started.
Database opened.
SQL> conn yyf123/yyf123
Connected.
SQL> select count from emp;
COUNT(*)
----------
14
SQL> col object_name for a20;
select s.owner,s.object_name,s.object_type,status from dba_objects s where object_name='EMP' ;
OWNER OBJECT_NAME OBJECT_TYPESTATUS
------------------------------ -------------------- ------------------- -------
YYF123 EMP TABLEVALID
YYF123 EMP MATERIALIZED VIEWINVALID
这就是为什么要建立dblink 和mv了，到此也证实了。user rename 后物化视图会失效。
明天整理一下此物化视图INVALID的处理方法。