您好,登錄后才能下訂單哦!
11.2.0.2里新增了一個(gè)新特性——用戶(hù)重命名(Rename User),在這個(gè)版本之前要想重命名用戶(hù),需要按用戶(hù)導(dǎo)出,再fromuser touser(imp)或remap_schem(impdp)。還可以通過(guò)修改數(shù)據(jù)字典user$的方式來(lái)重命名用戶(hù),但這種方法可能會(huì)帶來(lái)風(fēng)險(xiǎn)。有了這個(gè)特性之后,可以通過(guò)alter user oldname rename to newname identified by "password";一條命令來(lái)重命名用戶(hù)。
下面來(lái)看測(cè)試過(guò)程,使用Oracle版本11.2.0.4
sys@ORCL>select * from v$version; BANNER ------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
1、創(chuàng)建測(cè)試用戶(hù)及測(cè)試表
sys@ORCL>create user sq identified by "sq"; User created. sys@ORCL>grant connect,resource to sq; Grant succeeded sys@ORCL>create table sq.t1 as select * from dba_tables; Table created.
2、執(zhí)行alter user語(yǔ)句
sys@ORCL>alter user sq rename to zlx identified by "zlx"; alter user sq rename to zlx identified by "zlx" * ERROR at line 1: ORA-00922: missing or invalid option
報(bào)錯(cuò)無(wú)效的操作,原來(lái)是需要修改隱含參數(shù)"_enable_rename_user"
3、修改隱含參數(shù)"_enable_rename_user",并以restrict模式重啟數(shù)據(jù)庫(kù)
sys@ORCL>alter system set "_enable_rename_user"=true scope=spfile; System altered. sys@ORCL>startup restrict force; ORACLE instance started. Total System Global Area 1620115456 bytes Fixed Size 2253704 bytes Variable Size 956304504 bytes Database Buffers 654311424 bytes Redo Buffers 7245824 bytes Database mounted. Database opened.
4、再次執(zhí)行alter user語(yǔ)句,重命名用戶(hù)
sys@ORCL>alter user sq rename to zlx identified by "zlx"; User altered. sys@ORCL>select count(*) from sq.t1; select count(*) from sq.t1 * ERROR at line 1: ORA-00942: table or view does not exist sys@ORCL>select count(*) from zlx.t1; COUNT(*) ---------- 2864 sys@ORCL>conn zlx/zlx ERROR: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege Warning: You are no longer connected to ORACLE. sys@ORCL>conn / as sysdba Connected. sys@ORCL>alter system disable restricted session; System altered. sys@ORCL>conn zlx/zlx Connected. zlx@ORCL>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------ --------------------- ---------- T1 TABLE
從上面的結(jié)果來(lái)看用戶(hù)名重命名成功。
5、reset隱含參數(shù)"_enable_rename_user"并重啟數(shù)據(jù)庫(kù)
sys@ORCL>alter system reset "_enable_rename_user";
System altered.
下面來(lái)看直接修改數(shù)據(jù)字典user$的方法:
sys@ORCL>select user#,name from user$ where name='ZLX';
USER# NAME
---------- --------
94 ZLX
sys@ORCL>update user$ set name='SQ' WHERE USER#=94;
1 row updated.
sys@ORCL>commit;
Commit complete.
sys@ORCL>conn sq/zlx;
Connected.
網(wǎng)上還提到update完后,需要alter system checkpoint;和alter system flush shared_pool;,從上面的操作來(lái)看好象更簡(jiǎn)單一些,但user$作為oracle的核心基表之一,修改數(shù)據(jù)字典會(huì)不會(huì)造成系統(tǒng)不穩(wěn)定產(chǎn)生ora-600錯(cuò)誤等等都不好說(shuō),所以生產(chǎn)上一定要慎重。
參考:http://www.linuxidc.com/Linux/2014-04/100166.htm
http://ylw6006.blog.51cto.com/470441/799261/
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。