New Features of Oracle 11g: User rename

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>