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