文章內(nèi)容

Oracle 11g修改數(shù)據(jù)庫用戶名

發(fā)布時間: 2012/9/3 16:41:58

最近翻看某blog，發(fā)現(xiàn)原來Oracle 11g提供了用戶重命名的新特性，在10g環(huán)境下，如果想對用戶重命名，一般來說是先創(chuàng)建一個新的用戶并授權(quán)，然后將原用戶下的所有對象導(dǎo)入，然后刪除舊的用戶！下面來在11g rac環(huán)境下介紹下這個新特性！
一：創(chuàng)建一個測試用戶xxx,并寫入測試數(shù)據(jù)，由spfile文件生成pfile文件，關(guān)閉rac數(shù)據(jù)庫

[Oracle@node1 ~]$ sqlplus sys/Ab123456@rac5 as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 19:42:12 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create user xxx identified by 123456 default tablespace users; User created. SQL> grant resource,connect to xxx; Grant succeeded. SQL> create table xxx.test as select * from dba_objects; Table created. SQL> alter user xxx rename to yyy; alter user xxx rename to yyy * ERROR at line 1: ORA-00922: missing or invalid option SQL> alter user xxx rename to yyy identified by 123456; alter user xxx rename to yyy identified by 123456 * ERROR at line 1: ORA-00922: missing or invalid option SQL> create pfile from spfile; File created. [Oracle@node1 ~]$ srvctl stop database -d rac -o immediate [oracle@node1 ~]$ srvctl status database -d rac Instance node1 is not running on node node1 Instance node2 is not running on node node2 二：修改pfile文件，添加隱含參數(shù) *._enable_rename_user='TRUE'，將數(shù)據(jù)庫以restrict方式啟動

[Oracle@node1 ~]$ cd /u01/app/oracle/product/11.2.0/db1/dbs/ [Oracle@node1 dbs]$ ls hc_node1.dat initnode1.ora init.ora orapwnode1 [Oracle@node1 dbs]$ tail -1 initnode1.ora *._enable_rename_user='TRUE' [Oracle@node1 dbs]$ sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 19:51:41 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> conn /as sysdba Connected to an idle instance. SQL> startup restrict pfile=$Oracle_HOME/dbs/initnode1.ora ORACLE instance started. Total System Global Area 1235959808 bytes Fixed Size 2227904 bytes Variable Size 805306688 bytes Database Buffers 419430400 bytes Redo Buffers 8994816 bytes Database mounted. Database opened. SQL> select open_mode,name from v$database; OPEN_MODE NAME -------------------- --------------- READ WRITE RAC SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string 三：修改xxx用戶名為yyy

SQL> alter user xxx rename to yyy; alter user xxx rename to yyy * ERROR at line 1: ORA-02000: missing IDENTIFIED keyword SQL> alter user xxx rename to yyy identified by 123456; User altered. SQL> select count(*) from yyy.test; COUNT(*) ---------- 74556 四：使用spfile啟動rac

SQL> shutdown immediate; Database closed. Database dismounted. Oracle instance shut down. [Oracle@node1 dbs]$ srvctl start database -d rac [oracle@node1 dbs]$ srvctl status database -d rac Instance node1 is running on node node1 Instance node2 is running on node node2 五：連接測試，由此可見，對用戶的rename操作，可以繼承原有的權(quán)限

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 20:00:52 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- TEST TABLE [Oracle@node1 ~]$ sqlplus xxx/123456@rac5 SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 6 20:01:22 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied

本文出自：億恩科技【mszdt.com】

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]