Oracle 11g新特性之用户重命名
我们在项目开发中,数据也会不断变化,因此需要定期将开发库数据库导入到测试数据库中。通常的做法是“三部曲:”
1.从开发库中exp导出数据
2.删除测试库用户
3.使用imp把导出数据导入到测试库
今天同事问我可不可以保留之前的用户,比如给用户改个名称。我之前倒没想过这个问题,说应该可以吧,使用alter user *** rename to ***;语句。需要上机验证一下,一操作就傻眼了,Oracle 10g不支持用户重命名,从Oracle 11.2.0.2才开始提供用户重命名的新特性。
Oracle 10g 不支持用户重命名
1.环境准备
我们在Oracle 10g中进行试验。点击(此处)折叠或打开
-
C:\\Users\\Administrator>sqlplus sys/hoegh as sysdba
-
-
SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 5月 14 09:17:02 2015
-
-
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
-
-
-
连接到:
-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
SQL>
-
SQL> select * from v$version;
-
-
BANNER
-
----------------------------------------------------------------
-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
-
PL/SQL Release 10.2.0.4.0 - Production
-
CORE 10.2.0.4.0 Production
-
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
-
NLSRTL Version 10.2.0.4.0 - Production
-
- SQL>
2.重命名用户报错
执行alter user *** rename to ***;语句,数据库报错,如下所示:点击(此处)折叠或打开
-
SQL>
-
SQL> alter user scott rename to tiger;
-
alter user scott rename to tiger
-
*
-
第 1 行出现错误:
-
ORA-00922: 选项缺失或无效
-
-
-
SQL>
-
SQL> alter user scott rename to tiger identified by scott;
-
alter user scott rename to tiger identified by scott
-
*
-
第 1 行出现错误:
-
ORA-00922: 选项缺失或无效
-
-
-
SQL>
- SQL>
Oracle 11g用户重命名
1.环境准备
点击(此处)折叠或打开
-
SQL>
-
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
-
- SQL>
2.修改Oracle的隐含参数"_enable_rename_user"
通常,在sqlplus中使用show parameter xx可以查看到Oracle定义的参数, 它是通过查询v$parameter获得的。 另外Oracle中还有一些隐含的参数 无法直接通过show parameter的方式查询,也就是我们接下来使用到的隐含参数。修改隐含参数时, 使用alter system set "parameter_name"=value scope=both;其中有些可以在memory更改而有些仅仅可以通过spfile更改, 试试就知道了。需要注意的是一定要加上双引号, 另外引号内不能有空格, 只能包含参数的名字。点击(此处)折叠或打开
-
SQL>
-
SQL> show parameter process --通过show parameter查看参数
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
aq_tm_processes integer 1
-
cell_offload_processing boolean TRUE
-
db_writer_processes integer 1
-
gcs_server_processes integer 0
-
global_txn_processes integer 1
-
job_queue_processes integer 1000
-
log_archive_max_processes integer 4
-
processes integer 150
-
processor_group_name string
-
SQL>
-
SQL> show parameter enable_rename --无法通过show parameter查看隐含参数
-
SQL> show parameter rename
-
SQL>
-
SQL>
-
SQL> alter system set \"_enable_rename_user\"=true scope=spfile;
-
-
System altered.
-
- SQL>
3.用RESTRICTED模式启动数据库
用户重命名操作必须在RESTRICTED模式下完成。需要注意的是RESTRICTED模式以后 除了管理员都不能登录,如果需要非管理员登录,必须授予权限GRANT restricted session to username;
点击(此处)折叠或打开
-
SQL>
-
SQL> startup restrict force
-
ORACLE instance started.
-
-
Total System Global Area 941600768 bytes
-
Fixed Size 1348860 bytes
-
Variable Size 629148420 bytes
-
Database Buffers 306184192 bytes
-
Redo Buffers 4919296 bytes
-
Database mounted.
-
Database opened.
-
SQL>
-
SQL>
-
SQL> select status from v$instance;
-
-
STATUS
-
------------
-
OPEN
-
-
SQL>
-
SQL> select open_mode,name from v$database;
-
-
OPEN_MODE NAME
-
-------------------- ---------
-
READ WRITE HOEGH
-
- SQL>
4.修改用户名
点击(此处)折叠或打开
-
SQL>
-
SQL> alter user scott rename to tiger;
-
alter user scott rename to tiger
-
*
-
ERROR at line 1:
-
ORA-02000: missing IDENTIFIED keyword
-
-
-
SQL> alter user scott rename to tiger identified by scott;
-
-
User altered.
-
- SQL>
5.重启数据库
点击(此处)折叠或打开
-
SQL>
-
SQL> shutdown immediate
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SQL>
-
SQL>
-
SQL> startup
-
ORACLE instance started.
-
-
Total System Global Area 941600768 bytes
-
Fixed Size 1348860 bytes
-
Variable Size 629148420 bytes
-
Database Buffers 306184192 bytes
-
Redo Buffers 4919296 bytes
-
Database mounted.
-
Database opened.
- SQL>
6.确认结果
点击(此处)折叠或打开
-
SQL> conn scott/tiger
-
ERROR:
-
ORA-01017: invalid username/password; logon denied
-
-
-
Warning: You are no longer connected to ORACLE.
-
SQL>
-
SQL> conn tiger/scott
-
Connected.
-
SQL>
-
SQL> select * from cat;
-
-
TABLE_NAME TABLE_TYPE
-
------------------------------ -----------
-
BONUS TABLE
-
DEPT TABLE
-
EMP TABLE
-
HOEGH TABLE
-
SALGRADE TABLE
-
- SQL>
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。