oracle-databasedatabase-administrationoracle19c

Moving LOB size more than actual size


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 ?


Solution

  • 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.