11G利用隐含参数，修改用户名

步骤概述：

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的处理方法。

巴特西

11G利用隐含参数，修改用户名

最新文章

热门文章