Shulou(Shulou.com)06/01 Report--

A new feature has been added in 11.2.0.2-user renaming (Rename User). To rename a user before this version, you need to export by user and then fromuser touser (imp) or remap_schem (impdp). You can also rename a user by modifying the data dictionary user$, but this approach can be risky. Once you have this feature, you can rename the user with a command called alter user oldname rename to newname identified by "password";.

Let's look at the testing process, using Oracle version 11.2.0.4

Sys@ORCL > select * from v$version BANNER----Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionPL/SQL Release 11.2.0.4.0-ProductionCORE 11.2.0.4 .0 ProductionTNS for Linux: Version 11.2.0.4.0-ProductionNLSRTL Version 11.2.0.4.0-Production

1. Create test users and test tables

Sys@ORCL > create user sq identified by "sq"; User created.sys@ORCL > grant connect,resource to sq; Grant succeededsys@ORCL > create table sq.t1 as select * from dba_tables; Table created.

2. Execute the alter user statement

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

Invalid operation that reported an error. It turns out that the implicit parameter "_ enable_rename_user" needs to be modified.

3. Modify the implicit parameter "_ enable_rename_user" and restart the database in restrict mode

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 bytesFixed Size 2253704 bytesVariable Size 956304504 bytesDatabase Buffers 654311424 bytesRedo Buffers 7245824 bytesDatabase mounted.Database opened.

4. Execute the alter user statement again to rename the user

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/zlxERROR:ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege Warning: You are no longer connected to ORACLE.sys@ORCL > conn / as sysdbaConnected.sys@ORCL > alter system disable restricted session; System altered. Sys@ORCL > conn zlx/zlxConnected.zlx@ORCL > select * from tab TNAME TABTYPE CLUSTERID---T1 TABLE

From the above results, we can see that the user name was renamed successfully.

5. Reset implied parameter "_ enable_rename_user" and restart the database

Sys@ORCL > alter system reset "_ enable_rename_user"

System altered.

Here's how to directly modify the data dictionary 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.

It is also mentioned on the Internet that after update is finished, it seems easier to need alter system checkpoint; and alter system flush shared_pool;, from the above operation, but user$ as one of the core base tables of oracle, it is difficult to say whether modifying the data dictionary will cause system instability, ora-600 errors, and so on, so we must be cautious in production.

Reference: http://www.linuxidc.com/Linux/2014-04/100166.htm

Http://ylw6006.blog.51cto.com/470441/799261/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.