Moving LOB size more than actual size

Question

SELECT BYTES/ (1024*1024*1024) GB
 FROM DBA_SEGMENTS D
 JOIN DBA_LOBS L ON L.SEGMENT_NAME = D.SEGMENT_NAME
WHERE D.OWNER = 'SCHEMA_NAME' AND SEGMENT_NAME = 'MY_LOB'

GB
100

Actual LOB object size = 100 GB, but when moving LOB to another tablespace it takes up more space than 100 GB.

Oracle version 19c.

How to find the exact moving size LOB object ?

Answer 1

A couple of possibilities

1 - you are not taking into account the LOB index segment (although these are typically small relative to the LOB segment itself)

2 - you are comparing the sum of the lob contents with the anticipated segment size. Depending on your characterset, this can be out by a factor 2, eg

SQL> select * from dba_segments
  2  where tablespace_name = 'LARGETS';

no rows selected

SQL> select * from dba_segments
  2  where tablespace_name = 'LARGETS';

no rows selected

SQL>
SQL> create table t
  2  ( x int, c clob ) tablespace largets;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 1024*1024*1024/32000;

33554 rows created.

SQL> commit;

Commit complete.

SQL> select sum(dbms_lob.getlength(c))
  2  from t;

SUM(DBMS_LOB.GETLENGTH(C))
--------------------------
                1073728000

SQL> select segment_name
  2  from   user_lobs
  3  where  table_name = 'T';

SEGMENT_NAME
------------------------------
SYS_LOB0000177878C00002$$

SQL> select bytes/ (1024*1024) gb
  2  from dba_segments d
  3  where segment_name = 'SYS_LOB0000177878C00002$$';

        GB
----------
   2240.25

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'LARGETS';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                 2097152
SYS_IL0000177878C00002$$            65536
SYS_LOB0000177878C00002$$      2349072384

LOBS in multibyte characterset databases are stored in UCS2 format, so 2 bytes per character.