sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

学习笔记:Oracle用户管理 数据库user用户名称重命名的方法 Oracle11G

时间:2016-06-22 17:57   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 ORACLE用户重命名,Oracle数据库中已存在的user用户,想重命名Oracle用户的名称。Oracle 11G新特性,在线进行更改

从oracle 11.2.0.2开始提供了用户重命名的新特性,在以前的版本中,如果想对用户重命名,一般来说是先创建一个新的用户并授权,然后将原用户下的所有对象导入,然后删除旧的用户!

1.查询Oracle数据库版本信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

2.创建Oracle用户

SQL> create user oracleplus identified by oracleplus;

User created.

SQL> grant connect,resource to oracleplus;

Grant succeeded.

SQL> conn oracleplus/oracleplus
Connected.

SQL> create table t_oracleplus   as select * from user_users;

Table created.

SQL> create index ind_t_oracleplus on t_oracleplus(user_id);

Index created.

SQL> conn / as sysdba
Connected.
SQL> select object_type,object_name from dba_objects where owner='oracleplus';

OBJECT_TYPE         OBJECT_NAME
------------------- ---------------------------------------------------------
TABLE               T_oracleplus
INDEX               IND_T_oracleplus

2.重命名Oracle用户user名称

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

--默认值是false
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
  4    5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%¶m%')
  7  order by name
  8  /
Enter value for param: _enable_rename_user
old   6:    and upper(a.ksppinm) LIKE upper('%¶m%')
new   6:    and upper(a.ksppinm) LIKE upper('%_enable_rename_user%')

NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ ------------------------------------------------
_enable_rename_user              FALSE                    enable RENAME-clause using ALTER USER statement

SQL> startup force restrict
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.

--_enable_rename_user=false,在restrict模式也不能修改用户名
SQL> ALTER user XFF RENAME TO xffei IDENTIFIED BY oracleplus;
ALTER user XFF RENAME TO xffei IDENTIFIED BY oracleplus
               *
ERROR at line 1:
ORA-00922: missing or invalid 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 restrict                                     
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> ALTER user oracleplus RENAME TO xff IDENTIFIED BY oracleplus;

User altered.

测试结果

SQL> startup force
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> select object_type,object_name from dba_objects where owner='oracleplus';

no rows selected

SQL> select object_type,object_name from dba_objects where owner='XFF';

OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
TABLE               T_oracleplus
INDEX               IND_T_oracleplus

SQL> conn xff/oracleplus
Connected.
SQL> select count(*) from t_oracleplus;

  COUNT(*)
----------
         1

相关文档和上面的测试,得出结论:数据库版本在11.2.0.2及其以上版本,_enable_rename_user设置为true,数据库启动到restrict模式可以修改用户名


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle用户管理 数据库user用户名称重命名的方法 Oracle11G

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/475.html

Oracle研究中心

关键词:

Oracle 11G新特性

Oracle用户管理

Oracle数据库user用户名称重命名的方法