Oracle expdp 테스트해보자 ORA-01950: no privileges on tablespace 'USERS' 발생

리눅스 7.6 환경에 Oracle 12.2에서 해당 테스트를 진행해보았다.

TEST를 위한 유저생성 및 권한부여

[oracle@ORACLE12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 16 09:18:38 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create user datapump identified by datapump;

User created.

SQL> grant connect, resource, select any table to datapump; 

Grant succeeded.

SQL> grant exp_full_database, imp_full_database to datapump;

Grant succeeded.

expdp받을 해당위치로 물리적인 디렉토리 생성

[oracle@ORACLE12 datapump]$ pwd
/home/oracle/test/datapump

datapump경로 생성 및 확인

[oracle@ORACLE12 datapump]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 16 09:23:43 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create directory datapumptest as '/home/oracle/test/datapump';

Directory created.

SQL> select * from dba_directories 
  2  where directory_name='DATAPUMPTEST';

OWNER		     DIRECTORY_NAME		    DIRECTORY_PATH				       ORIGIN_CON_ID
-------------------- ------------------------------ -------------------------------------------------- -------------
SYS		     DATAPUMPTEST		    /home/oracle/test/datapump					   0

SQL> grant read, write on directory datapumptest to datapump;

Grant succeeded.

expdp full 진행.

[oracle@ORACLE12 datapump]$ expdp datapump/datapump dumpfile=test_full.dmp full=y directory=datapumptest

Export: Release 12.2.0.1.0 - Production on Wed Dec 16 09:27:51 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "DATAPUMP.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1161
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "SYS.KUPV$FT", line 1054
ORA-06512: at "SYS.KUPV$FT", line 1042

## 해당 에러는 방금 생성한 TEST유저가 Tablespace에 대한 quota가 할당되지 않아 발생한다. ##

해당 유저가 사용하는 TABLESPACE 조회

SQL> select username, default_tablespace from dba_users
  2  where username='DATAPUMP'

USERNAME	     DEFAULT_TABLESPACE
-------------------- ------------------------------
DATAPUMP	     USERS

해당 TABLESPACE에 대해 quota를 할당한다.
현재는 TEST용으로 사용하는 DB라 사용량이 작아 그냥 100M를 주었다.

SQL> alter user datapump quota 100m on users;

User altered.


아래의 명령으로 할당제한을 풀 수 있다.
ALTER USER <user> quota unlimited on <tablespace name>;

[oracle@ORACLE12 datapump]$ expdp datapump/datapump dumpfile=test_full.dmp full=y directory=datapumptest

Export: Release 12.2.0.1.0 - Production on Wed Dec 16 09:33:24 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DATAPUMP"."SYS_EXPORT_FULL_01":  datapump/******** dumpfile=test_full.dmp full=y directory=datapumptest 
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
.
.
.
<중략>
.
.
.
******************************************************************************
Dump file set for DATAPUMP.SYS_EXPORT_FULL_01 is:
  /home/oracle/test/datapump/test_full.dmp
Job "DATAPUMP"."SYS_EXPORT_FULL_01" successfully completed at Wed Dec 16 09:34:31 2020 elapsed 0 00:01:02

해당 테스트는 데이터가 없어 1분만에 끝났지만, 경험상 segment가 약 80g 정도 되는 DB의 경우 10분 내외로 FULL 받을 수 있고
dmp받은 파일은 약 25g정도 되었다.
하지만 서버의 사양에 따라 조금씩 다를 수 있다.

expdp완료 후 dmp 파일과 log파일

[oracle@ORACLE12 datapump]$ ll
total 33328
-rw-r--r-- 1 oracle dba       79 Dec 16 09:31 afiedt.buf
-rw-r--r-- 1 oracle dba     8498 Dec 16 09:34 export.log
-rw-r----- 1 oracle dba 34111488 Dec 16 09:34 test_full.dmp

dmp파일은 이름을 지정해주어 원하는 이름으로 생성되었지만, 
log 파일은 다른 옵션을 주지않아 default로 생성되었다.

TEST 완료 후 TEST로 발생한 잔여물 삭제

[oracle@ORACLE12 test]$ rm -rf datapump/
[oracle@ORACLE12 test]$ ll
total 0
[oracle@ORACLE12 test]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 16 09:47:10 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop user datapump cascade;

User dropped.

오늘은 expdp를 test할 유저를 새로 생성하여 test해보았고,

 

ORA-01950: no privileges on tablespace 'USERS' 발생에 대해 다루었다.