프린트 하기

OS환경 : Oracle Linux 5.8 (64bit)


DB 환경 : Oracle Database 10.2.0.5, 19.6.0.0


방법 : 오라클 일반 테이블과 blob 테이블 생성, 데이터 삭제 후 datapump 테스트

테스트 내용

1. 일반 테이블 테스트

일반 테이블의 segment를 약 2GB로 만든 뒤 아래 항목을 비교함

1_1. datapump로 expdp estimate_only=yes 의 예상용량

1_2. datapump로 expdp 한 뒤 만들어진 실제용량


일반 테이블을 delete 구문으로 50%를 삭제한 뒤 아래 항목을 비교함

1_3. datapump로 expdp estimate_only=yes 의 예상용량

1_4. datapump로 expdp 한 뒤 만들어진 실제용량



2. blob 테이블 테스트

blob 테이블의 segment를 약 2GB로 만든 뒤 아래 항목을 비교함

2_1. datapump로 expdp estimate_only=yes 의 예상용량

2_2. datapump로 expdp 한 뒤 만들어진 실제용량


blob 테이블을 delete 구문으로 50%를 삭제한 뒤 아래 항목을 비교함

2_3. datapump로 expdp estimate_only=yes 의 예상용량

2_4. datapump로 expdp 한 뒤 만들어진 실제용량



환경구성

datapump용 디렉토리 생성

1
2
3
4
5
6
7
SQL> create directory hwmdir as '/home/oracle/';
 
Directory created.
 
SQL> grant read, write on directory hwmdir to public;
 
Grant succeeded.



테이블 스페이스 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> create tablespace hwmtest_ts datafile '/oracle/app/oracle/oradata/ORCL10/hwmtest_ts01.dbf' size 3g autoextend off;
 
Tablespace created.
 
SQL> select name from v$tablespace;
 
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
HWMTEST_TS
 
6 rows selected.



유저 생성

1
2
3
4
5
6
7
SQL> create user hwmuser identified by hwmuser account unlock default tablespace hwmtest_ts quota unlimited on hwmtest_ts;
 
User created.
 
SQL> grant resource, connect to hwmuser;
 
Grant succeeded.



1. 일반 테이블 테스트

테이블(nologging) 생성 및 데이터 삽입

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> conn hwmuser/hwmuser
Connected.
 
SQL> create table hwm_table (c1 varchar2(10), c2 varchar2(10), c3 varchar2(10));
 
Table created.
 
SQL> insert into hwm_table values ('AAAAAAAAAA','BBBBBBBBBB','CCCCCCCCCC');
 
1 row created.
 
SQL> insert into hwm_table select * from hwm_table;
 
1 row created.
 
SQL> /
SQL> /
16777216 rows created 까지 반복
.
SQL> /
 
16777216 rows created.
 
rownum 기준으로 16000000 row insert 
SQL> insert into hwm_table select * from hwm_table where rownum <=16000000;
 
16000000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*from hwm_table;
 
  COUNT(*)
----------
  49554432



세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE');
 
PL/SQL procedure successfully completed.



용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE         262144      2048

테이블이 2GB로 만들어짐



실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct 
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table;
 
      used
----------
    259448



1_1. datapump로 expdp estimate_only=yes 의 예상용량 확인

datapump로 expdp 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table1_1.log tables=hwm_table estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 10:55:05
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table1_1.log tables=hwm_table estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE"                           2 GB
Total estimation using BLOCKS method: 2 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 10:55:06

예상용량(estimated)을 2GB로 측정함



1_2. datapump로 expdp 한 뒤 만들어진 실제용량 확인

datapump로 expdp 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table1_2.dat logfile=hwm_table1_2.log tables=hwm_table
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 10:57:13
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table1_2.dat logfile=hwm_table1_2.log tables=hwm_table 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE"                       1.707 GB 49554432 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table1_2.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 10:57:27

예상용량(estimated)은 2GB 이지만

실제 export 된 용량은 1.7GB임



다음 테스트를 위해 hwm_table의 데이터 50% delete

1
2
3
SQL> delete hwm_table where rownum <= 24777216;
 
24777216 rows deleted.



ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' 가 발생하는경우

undo tbs가 작아서 한번에 삭제가 안된다면 아래 방법 사용(4번에 나눠서 작업)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table where rownum <= 6194304;
 
6194304 rows deleted.



테이블 count 확인

1
2
3
4
5
SQL> select count(*from hwm_table;
 
  COUNT(*)
----------
  24777216

49554432 에서 24777216(50%)만 남음



세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE');
 
PL/SQL procedure successfully completed.



용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE         262144      2048

dba_segments의 blocks와 용량은 변함없음



실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct 
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table;
 
      used
----------
    129725



delete 전 후 실제사용 BLOCK 비교

delete 작업 후 테이블 DBA_SEGMETNS의 BLOCK 수는 delete 작업 전과 동일하지만(262144)

실제 사용 BLOCK은 259448에서 129725로 줄어든것을 확인할 수 있음


이는 HWM 까지 사용하고 있다고 표시하고 있기 때문

이렇게 블록의 크기와 실제사용 블록의 크기가 차이가 많이 나면 HWM를 줄여주는 것이 좋음

(참조 https://positivemh.tistory.com/350)



1_3. datapump로 expdp estimate_only=yes 의 예상용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table1_3.log tables=hwm_table estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 11:49:24
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table1_3.log tables=hwm_table estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE"                           2 GB
Total estimation using BLOCKS method: 2 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 11:49:28

50%을 지웠지만 예상용량(estimated)을 2GB로 측정함



1_4. datapump로 expdp 한 뒤 만들어진 실제용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table1_4.dat logfile=hwm_table1_4.log tables=hwm_table
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 11:53:30
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table1_4.dat logfile=hwm_table1_4.log tables=hwm_table 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE"                       874.2 MB 24777216 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table1_4.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 11:53:42

예상용량(estimated)은 2GB 이지만

실제 export 된 용량은 874MB임



결론

datapump 시 estimate_only로 나온 예상용량은 dba_segments 의 bytes 용량과 동일하게 나오지만

실제로 datapump로 받아진 용량은 bytes 용량보다 같거나 더 작을수 있음



blob 테이블 테스트

일반 테이블을 담은 테이블 스페이스를 재사용할 예정이기 때문에 

먼저 일반 테이블 삭제

1
2
3
SQL> drop table hwm_table purge;
 
Table dropped.



blob 테이블 생성 및 데이터 삽입

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SQL> conn hwmuser/hwmuser
Connected.
 
SQL> create table hwm_table_blob (c1 blob, c2 blob, c3 blob);
 
Table created.
 
SQL> insert into hwm_table_blob
values (utl_raw.cast_to_raw('hello1'),utl_raw.cast_to_raw('hello2'),utl_raw.cast_to_raw('hello3'));
 
1 row created.
 
SQL> insert into hwm_table_blob select * from hwm_table_blob;
 
1 row created.
 
SQL> /
SQL> /
4194304 rows created 나올때 까지 반복
.
SQL> /
 
4194304 rows created.
 
rownum 기준으로 6700000 row insert 
SQL> insert into hwm_table_blob select * from hwm_table_blob where rownum <= 6700000;
 
6700000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*from hwm_table_blob;
 
  COUNT(*)
----------
  15088608



세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE_BLOB');
 
PL/SQL procedure successfully completed.



용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE_BLOB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE_BLOB         286720      2240

테이블이 2.1GB로 만들어짐



실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct 
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table_blob;
 
      used
----------
    279419



2_1. datapump로 expdp estimate_only=yes 의 예상용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table2_1.log tables=hwm_table_blob estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 15:42:23
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table2_1.log tables=hwm_table_blob estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE_BLOB"                  2.187 GB
Total estimation using BLOCKS method: 2.187 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 15:42:24

예상용량(estimated)을 2.1GB로 측정함



2_2. datapump로 expdp 한 뒤 만들어진 실제용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob
 
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 05 August, 2020 15:44:01
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/'********' directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.187 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  474.8 MB 15088608 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_2.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 15:44:29

예상용량(estimated)은 2.1GB 이지만

실제 export 된 용량은 470MB임

아직 delete를 하지도 않았는데 blob 타입이어서 그런지 datapump 실행시 2.1GB에서 470MB로 줄었음



다음 테스트를 위해 hwm_table_blob의 데이터 50% delete

1
2
3
4
5
6
7
SQL> delete hwm_table_blob where rownum <= 7544304;
 
7544304 rows deleted.
 
SQL> commit;
 
Commit complete.



ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' 가 발생하는경우

undo tbs가 작아서 한번에 삭제가 안된다면 아래 방법 사용(4번에 나눠서 작업)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete hwm_table_blob where rownum <= 1886076;
 
1886076 rows deleted.



테이블 count 확인

1
2
3
4
5
SQL> select count(*from hwm_table_blob;
 
  COUNT(*)
----------
   7544304

15088608에서 7544304(50%)만 남음



세션2 sys 계정

통계정보 수집

1
2
3
SQL> exec dbms_stats.gather_table_stats('HWMUSER','HWM_TABLE_BLOB');
 
PL/SQL procedure successfully completed.



용량 확인

1
2
3
4
5
6
7
8
9
SQL> 
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE_BLOB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE_BLOB         286720      2240

dba_segments의 blocks와 용량은 변함없음



실제사용 BLOCK 확인

1
2
3
4
5
6
7
8
9
SQL> 
select count(distinct
dbms_rowid.rowid_block_number(rowid)||
dbms_rowid.rowid_relative_fno(rowid)) "used"
from hwmuser.hwm_table_blob;
 
      used
----------
    139710



delete 전 후 실제사용 BLOCK 비교

delete 작업 후 테이블 DBA_SEGMETNS의 BLOCK 수는 delete 작업 전과 동일하지만(286720)

실제 사용 BLOCK은 279419에서 139710로 줄어든것을 확인할 수 있음


이는 HWM 까지 사용하고 있다고 표시하고 있기 때문

이렇게 블록의 크기와 실제사용 블록의 크기가 차이가 많이 나면 HWM를 줄여주는 것이 좋음

(참조 https://positivemh.tistory.com/350)



2_3. datapump로 expdp estimate_only=yes 의 예상용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ expdp hwmuser/hwmuser directory=hwmdir logfile=hwm_table2_3.log tables=hwm_table_blob estimate_only=yes
 
Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 06 August, 2020 17:28:54
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir logfile=hwm_table2_3.log tables=hwm_table_blob estimate_only=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "HWMUSER"."HWM_TABLE_BLOB"                  2.187 GB
Total estimation using BLOCKS method: 2.187 GB
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 17:28:57

50%을 지웠지만 예상용량(estimated)을 2.1GB로 측정함



2_4. datapump로 expdp 한 뒤 만들어진 실제용량

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob
 
Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 06 August, 2020 17:29:13
 
Copyright (c) 20032007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.187 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  237.4 MB 7544304 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_4.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at 17:29:29

예상용량(estimated)은 2.1GB 이지만

실제 export 된 용량은 237MB임



delete 전 일반 테이블과 blob 테이블 dba_segments blobks, 용량 비교(1-1, 1-2, 2-1, 2-2 부분)

1
2
3
4
5
6
7
8
9
10
SQL>
col segment_name for a16
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name in ('HWM_TABLE','HWM_TABLE_BLOB');
 
SEGMENT_NAME         BLOCKS        MB
---------------- ---------- ----------
HWM_TABLE              262144      2048
HWM_TABLE_BLOB         286720      2240



delete 전 일반 테이블과 blob 테이블 데이터 건수 비교(1-1, 1-2, 2-1, 2-2 부분)

1
2
. . exported "HWMUSER"."HWM_TABLE"                       874.2 MB 24777216 rows
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  474.8 MB 15088608 rows

dba_segments 기준 같은 2GB를 export 했을 때 

rows를 보면 일반테이블은 2천4백만건, blob 테이블은 1천5백만건 정도임

rows 가 차이 나지만 실제 사용 blocks 수는 별반차이가 나지않음 그에따라 용량도 비슷함

blob 데이터 건수가 일반테이블 건수보다 작아서 실제 export 시 받아지는 용량도 작음

데이터 건수를 동일하게 맞추면 비슷한 용량으로 받아짐



일반 테이블과 blob 테이블의 용량 차이가 나는 이유는

blob 테이블에 데이터 삽입시 사용한 utl_raw.cast_to_raw() 함수 때문임

12자리값으로 변환해서 insert 한것

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>
col before for a20
col after for a20
select 'hello1' as "before", utl_raw.cast_to_raw('hello1') as "after" from dual;
 
before             after
-------------------- --------------------
hello1             68656C6C6F31
 
select utl_raw.cast_to_varchar2('68656C6C6F31') as "before" from dual;
 
before
--------------------
hello1



문자열 수 때문에 차이가 나는것 같아서(일반테이블 varchar2(10), blob 데이터 12자리)

이후 일반테이블 insert 시 varchar2(12)로 만든 뒤 테스트를 해보았지만 

똑같이 blob 테이블(utl_raw.cast_to_raw로 insert)이 용량을 더 많이 차지하는 것을 확인하였음



결론

datapump 시 estimate_only로 나온 예상용량은 dba_segments 의 bytes 용량과 동일하게 나오지만

실제로 datapump로 받아진 용량은 bytes 용량보다 같거나 더 작을수 있음

blob 데이터(정확히는 utl_raw.cast_to_raw로 삽입된 데이터)가 일반 데이터보다 용량을 더 많이 차지함

(blob 데이터에 블록을 더 할당하는듯함)

19c 에서도 똑같이 blob 형식의 데이터를 delete 했을 때 실제 사용 BLOCK과 expdp 시 용량이 줄어듬



참고 - 19c에서 똑같은 방식으로 테스트 한 내용

실제 사용 BLOCK 수 비교

19c 에서도 delete 작업 후 테이블 DBA_SEGMETNS의 BLOCK 수는 delete 작업 전과 동일하지만

실제 사용 BLOCK은 270601에서 148919로 줄어든것을 확인할 수 있음



datapump 비교

19c delete 전 expdp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob
 
Export: Release 19.0.0.0.0 - Production on Wed Aug 5 17:29:42 2020
Version 19.3.0.0.0
 
Copyright (c) 19822019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/'********' directory=hwmdir dumpfile=hwm_table2_2.dat logfile=hwm_table2_2.log tables=hwm_table_blob 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  528.0 MB 16777216 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_2.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 17:32:57 2020 elapsed 0 00:03:07

528MB export 됨



19c delete 후 expdp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp hwmuser/hwmuser directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob
 
Export: Release 19.0.0.0.0 - Production on Wed Aug 5 17:50:04 2020
Version 19.3.0.0.0
 
Copyright (c) 19822019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HWMUSER"."SYS_EXPORT_TABLE_01":  hwmuser/******** directory=hwmdir dumpfile=hwm_table2_4.dat logfile=hwm_table2_4.log tables=hwm_table_blob 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HWMUSER"."HWM_TABLE_BLOB"                  290.5 MB 9232912 rows
Master table "HWMUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HWMUSER.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hwm_table2_4.dat
Job "HWMUSER"."SYS_EXPORT_TABLE_01" successfully completed at Wed Aug 5 17:51:56 2020 elapsed 0 00:01:50

290MB export 됨

delete 전에는 528MB 였지만 delete 후 약 50%정도 줄어듬



참고용

데이터 삽입시간이 너무 오래걸려서 취소한 내용(blob 테이블에 이미지 넣는 구문)




참조 : https://seul96.tistory.com/166

https://hrjeong.tistory.com/230

https://positivemh.tistory.com/183

https://docs.oracle.com/database/121/SUTIL/GUID-3DF4A0F1-385B-4749-99D3-A9C3F9706C0B.htm#SUTIL846

https://positivemh.tistory.com/377

http://dbcafe.co.kr/wiki/index.php/%EC%98%A4%EB%9D%BC%ED%81%B4_LOB_%EC%9D%B4%EA%B4%80

https://positivemh.tistory.com/356

https://docs.oracle.com/database/121/WSMQG/app_urpkg.htm#WSMQG293