오라클 19c 유저 이름 변경 방법

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c 유저 이름 변경(username 변경)

PostgreSQL의 경우 ALTER USER TEST3 RENAME TO TEST4; 와 같이 유저 명을 변경할 수 있지만

오라클의 경우 유저 명을 변경하는 명령이 따로 존재하지 않음

그래서 오라클에서 유저 명을 변경하려면 우회방식을 이용해야함

1. datapump remap_schema 파라미터 이용 방법

2. user$ 직접 수정 방법(권고 및 지원되지 않음)

 

 

1. datapump remap_schema 파라미터 이용 방법

chuser1 유저를 chuser2으로 변경하는 시나리오

 

chuser1 유저 생성 및 권한 부여

SQL> create user chuser1 identified by chuser1 account unlock default tablespace users quota unlimited on users;
 
User created.
 
SQL> grant resource, connect to chuser1;
 
Grant succeeded.

 

 

유저 접속 후 샘플 테이블, 데이터 생성

SQL> conn chuser1/chuser1
 
SQL> 
create table testtb(cola varchar2(20), colb number, colc number, 
cold varchar2(30), cole varchar2(30), colf varchar2(30), 
colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30)) tablespace users;
 
Table created.
 
SQL> 
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF testtb%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..50000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=999;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
   w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
   FORALL i in 1..50000 INSERT INTO testtb VALUES w_ins(i);
   COMMIT;
END;
/
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from testtb;
 
  COUNT(*)
----------
     50000

 

 

system 계정으로 접속 후 디렉토리 및 chuser2 생성, 권한 부여

SQL> conn system/oracle
SQL> create directory changeuser as '/home/oracle/changeuser';
 
Directory created.
 
SQL> !mkdir -p /home/oracle/changeuser
SQL> create user chuser2 identified by chuser2 account unlock default tablespace users quota unlimited on users;
 
User created.
 
SQL> grant resource, connect to chuser2;
 
Grant succeeded.

 

 

chuser1 데이터 expdp 로 export

$ expdp system/oracle directory=changeuser schemas=chuser1 dumpfile=ch1.dmp logfile=ch1expdp.log
 
Export: Release 11.2.0.4.0 - Production on Wed Jul 14 19:58:06 2021
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/***** directory=changeuser schemas=chuser1 dumpfile=ch1.dmp logfile=ch1expdp.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "CHUSER1"."TESTTB"                          7.579 MB   50000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/changeuser/ch1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jul 14 19:58:11 2021 elapsed 0 00:00:04

 

 

chuser1에서 chuser2로 impdp remap_schema 실행

$ impdp system/oracle directory=changeuser schemas=chuser1 remap_schema=chuser1:chuser2 dumpfile=ch1.dmp logfile=ch1impdp.log
 
Import: Release 11.2.0.4.0 - Production on Wed Jul 14 19:59:08 2021
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/****** directory=changeuser schemas=chuser1 remap_schema=chuser1:chuser2 dumpfile=ch1.dmp logfile=ch1impdp.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CHUSER2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CHUSER2"."TESTTB"                          7.579 MB   50000 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Wed Jul 14 19:59:10 2021 elapsed 0 00:00:01

 

 

chuser2 접속 후 데이터 확인

SQL> conn chuser2/chuser2
SQL> select count(*) from testtb;
 
  COUNT(*)
----------
     50000

정상적으로 확인됨

 

 

기존 유저(chuser1) 삭제

SQL> drop user chuser1 cascade; 
 
User dropped.

 

 

2. user$ 직접 수정 방법(권고되지 않음)

chuser2 유저를 chuser3으로 변경하는 시나리오

 

user$ 뷰 확인

SQL> select user#, name from user$;
 
     USER# NAME
---------- ------------------------------
     0 SYS
     1 PUBLIC
     2 CONNECT
.
.
    35 CHUSER2

user# 35에 chuser2가 존재함

 

 

user# 35의 name을 chuser3으로 변경 후 commit 후 확인

SQL> update user$ set name='CHUSER3' where user#=35;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select user#, name from user$ where user#=35;
 
     USER# NAME
---------- ------------------------------
    35 CHUSER3

user# 35이 chuser3으로 변경됨

 

 

안전하게 db 재기동(하지않아도 변경한 계정 정상접속은됨 혹시모를 위험 때문에 재기동)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size         1543505816 bytes
Database Buffers      587202560 bytes
Redo Buffers            4923392 bytes
Database mounted.
Database opened.

 

 

계정 접속 후 데이터 확인(패스워드는 기존유저의 패스워드)

SQL> conn chuser3/chuser2;
Connected.
SQL> select count(*) from testtb;
 
  COUNT(*)
----------
     50000

정상적으로 데이터가 확인됨

 

 

다시한번 언급하지만 user$ 를 변경하는건 권장되지 않는 작업이고

oracle에서도 지원하지 않는 방법임

usernane을 변경해야 한다면 첫번째 방법을 사용해야함

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-USER.html#GUID-9FCD038D-8193-4241-85CD-2F4723B27D44

http://www.gurubee.net/lecture/2940

http://www.orafaq.com/wiki/Oracle_database_FAQ#Can_one_rename_a_database_user_.28schema.29.3F

https://community.oracle.com/tech/developers/discussion/673334/can-one-rename-a-database-user-schema