New Features of Oracle 11g: User rename
Oracle Data is constantly changing during project development. Therefore, we need to regularly import the development database to the test database. The common practice is "trilogy :"
1. Export data from exp in the Development Library
2. Delete the Test Database User
3. Use imp to import exported data to the Test Database
Today, my colleague asked me if I could keep my previous users, for example, changing the user name. I have never thought about this before. You can use the alter user *** rename to ***; statement. You need to verify it on the machine. It is dumb to perform one operation. Oracle 10 Gb does not support user rename. The new features of user rename are provided from Oracle 11.2.0.2.
Oracle 10 Gb does not support user rename
1. Prepare the environment
We performed the test in Oracle 10 Gb.
C: \ Users \ Administrator> sqlplus sys/hoegh as sysdba
SQL * Plus: Release 10.2.0.4.0-Production on Thursday May 14 09:17:02 2015
Copyright (c) 1982,200 7, Oracle. All Rights Reserved.
Connect:
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. An error is returned when you rename a user.
Run the alter user *** rename to *** statement. The database reports an error as follows:
SQL>
SQL> alter user scott rename to tiger;
Alter user scott rename to tiger
*
Row 3 has an error:
ORA-00922: Option Missing or invalid
SQL>
SQL> alter user scott rename to tiger identified by scott;
Alter user scott rename to tiger identified by scott
*
Row 3 has an error:
ORA-00922: Option Missing or invalid
SQL>
SQL>
Oracle 11g user rename
1. Prepare the environment
We performed the test in Oracle 11g.
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. Modify the implicit parameter "_ enable_rename_user" of Oracle"
Generally, you can use show parameter xx in sqlplus to view the Oracle-defined parameters, which are obtained by querying v $ parameter. In addition, some implicit parameters in Oracle cannot be directly queried through the show parameter method, that is, the implicit parameters we will use next. When modifying implicit parameters, use alter system set "parameter_name" = value scope = both; some of them can be changed in memory, and some can be changed only through spfile. Note that double quotation marks must be added. In addition, there must be no space in the quotation marks and only the parameter name can be included.
Click (here) to fold or open
SQL>
SQL> show parameter process -- view parameters through 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 -- the implicit parameter cannot be viewed through show parameter
SQL> show parameter rename
SQL>
SQL>
SQL> alter system set \ "_ enable_rename_user \" = true scope = spfile;
System altered.
SQL>
3. Start the database in RESTRICTED mode
The user RENAME operation must be completed in RESTRICTED mode. It should be noted that after RESTRICTED mode, no administrator can log on. If you need to log on as a non-administrator, you must GRANT the restricted session to username;
Click (here) to fold or open
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. Modify the user name
During the RENAME operation, you must specify a new password. Otherwise, an error is returned.
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. Restart the database
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. Confirmation result
The original scott user has been renamed as the tiger user. Now, let's verify whether the tiger user can log on normally and whether the original scott user still exists.
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>