Renaming Oracle Database User

HOST : pald-p01
DBNAME : TESTDB

SQL> select username from dba_users where username like 'DMUS%';

USERNAME
--------------------------------------------------------------------------------
DMUSA01A
DMUSA01B

SQL> select name,password from sys.user$ where name like 'DMUS%';

NAME         PASSWORD
------------ ------------------------------
DMUSA01A     5B7F6AERTFT2E
DMUSA01B     320IJTSDB5D41



# Connect as SYS to your database
conn / as sysdba
# At first we set an undocumented parameter to enable the RENAME option
alter session set "_enable_rename_user"=true;
# Bring the Database to restricted session, in order to avoid Memory Problems for huge schema
alter system enable restricted session;
# Now lets RENAME the user DEMO to DEMO_NEW
# and provide a password for the new user DEMO_NEW
alter user DMUSA04 rename to DMUSP04 identified by values '3DCFE76EA7D55A80';
# Disable restricted session
alter system disable restricted session;



SQL> select name from v$database;

NAME
------------
TESTDB

SQL> alter session set "_enable_rename_user"=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 40G
sga_target                           big integer 40G
unified_audit_sga_queue_size         integer     1048576
SQL> set timing on


alter user DMUSA01A rename to DMUST01A identified by values '5B7F6AERTFT2E';
alter user DMUSA01B rename to DMUST01B identified by values '320IJTSDB5D41';



SQL> alter user DMUSA01A rename to DMUST01A identified by values '5B7F6AERTFT2E';

User altered.

Elapsed: 00:00:01.70
SQL> alter user DMUSA01B rename to DMUST01B identified by values '320IJTSDB5D41';

User altered.

Elapsed: 00:00:00.29

SQL> select name,password from sys.user$ where name like 'DMUS%';

NAME         PASSWORD
------------ ------------------------------
DMUST01A     5B7F6AERTFT2E
DMUST02B     320IJTSDB5D41


SQL> alter system disable restricted session;

System altered.

Elapsed: 00:00:00.17
SQL> alter session set "_enable_rename_user"=false;

Session altered.

Elapsed: 00:00:00.00


Before Rename
=============
select owner, object_type, object_name from dba_invalid_objects;

OWNER        OBJECT_TYPE                    OBJECT_NAME
------------ ------------------------------ ------------------------------

DMUSA01A     SYNONYM                        MD_ETL_BCH_SEQ
DMUSA01B     SYNONYM                        MD_ETL_BCH_SEQ
DMUSA01B     PACKAGE                        PKG_LOGICAL_L
DMUSA01B     PACKAGE                        PKG_INFC_UTIL
DMUSA01B     PACKAGE                        PKG_MNFST_MGMT
DMUSA01B     PACKAGE                        PKG_PPA_KSRN_GRP4
DMUSA01B     PACKAGE                        PKG_PPA_KSRN_GRP5
DMUSA01A     PROCEDURE                      P_RSET_INVL_HSDR_SCMA
DMUSA01A     PROCEDURE                      P_RSET_INVL_HSDR_TBL
DMUSA01B     PROCEDURE                      P_RSET_INVL_HSDR_SCMA
DMUSA01B     PROCEDURE                      P_RSET_INVL_HSDR_TBL
DMUSA01B     PACKAGE BODY                   IDS_SRVC_VENDING_PKG
DMUSA01B     PACKAGE BODY                   IDS_VDF_PKG
DMUSA01B     PACKAGE BODY                   PKG_IDS_REQ_GR_SVC
DMUSA01B     PACKAGE BODY                   PKG_INFC_UTIL
DMUSA01B     PACKAGE BODY                   PKG_LOGICAL_LOCK
DMUSA01B     PACKAGE BODY                   PKG_LQCS
DMUSA01B     PACKAGE BODY                   PKG_MNFST_MGMT
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP2
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP3
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP4
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP5

22 rows selected.

SQL>


After Rename
============
OWNER        OBJECT_TYPE                    OBJECT_NAME
------------ ------------------------------ ------------------------------
DMUSA01A     SYNONYM                        MD_ETL_BCH_SEQ
DMUSA01B     SYNONYM                        MD_ETL_BCH_SEQ
DMUSA01B     PACKAGE                        PKG_LOGICAL_L
DMUSA01B     PACKAGE                        PKG_INFC_UTIL
DMUSA01B     PACKAGE                        PKG_MNFST_MGMT
DMUSA01B     PACKAGE                        PKG_PPA_KSRN_GRP4
DMUSA01B     PACKAGE                        PKG_PPA_KSRN_GRP5
DMUSA01A     PROCEDURE                      P_RSET_INVL_HSDR_SCMA
DMUSA01A     PROCEDURE                      P_RSET_INVL_HSDR_TBL
DMUSA01B     PROCEDURE                      P_RSET_INVL_HSDR_SCMA
DMUSA01B     PROCEDURE                      P_RSET_INVL_HSDR_TBL
DMUSA01B     PACKAGE BODY                   IDS_SRVC_VENDING_PKG
DMUSA01B     PACKAGE BODY                   IDS_VDF_PKG
DMUSA01B     PACKAGE BODY                   PKG_IDS_REQ_GR_SVC
DMUSA01B     PACKAGE BODY                   PKG_INFC_UTIL
DMUSA01B     PACKAGE BODY                   PKG_LOGICAL_LOCK
DMUSA01B     PACKAGE BODY                   PKG_LQCS
DMUSA01B     PACKAGE BODY                   PKG_MNFST_MGMT
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP2
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP3
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP4
DMUSA01B     PACKAGE BODY                   PKG_PPA_KSRN_GRP5

22 rows selected.