select owner,TABLE_NAME,SEGMENT_NAME from dba_lobs where table_name='AD_STAGE_LOG_DTL' and owner='ADCI'; select bytes,owner,SEGMENT_NAME,bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000200510C00009$$' and owner='ADCI'; SELECT max(DBMS_LOB.GETLENGTH(FAIL_LOG))/1024/1024 as Size_MB FROM AD_STAGE_LOG_DTL; SELECT DBMS_LOB.GETLENGTH(FAIL_LOG)/1024/1024 as Size_MB FROM AD_STAGE_LOG_DTL where FAIL_LOG is not null order by 1;
通过oci方式导数据,会用到一个参数 LONGREADLEN,这个值默认是1MB,一行记录会分配1MB,N行记录就分配 N*1 MB。所以在通过ora2pg 导出lob字段的时候,可能会碰到这个报错,原因是导出的表中包含lob字段,且单字段大小超过了1MB,因此报错。
通过如下SQL在Oracle侧查出lob字段的大小:
select owner,TABLE_NAME,SEGMENT_NAME from dba_lobs where table_name='AD_STAGE_LOG_DTL' and owner='ADCI';
select bytes,owner,SEGMENT_NAME,bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000200510C00009$$' and owner='ADCI';
SELECT max(DBMS_LOB.GETLENGTH(FAIL_LOG))/1024/1024 as Size_MB FROM AD_STAGE_LOG_DTL;
SELECT DBMS_LOB.GETLENGTH(FAIL_LOG)/1024/1024 as Size_MB FROM AD_STAGE_LOG_DTL where FAIL_LOG is not null order by 1;
根据实际情况调整 ora2pg 配置文件中的参数 LONGREADLEN,通常来说,与lob字段的max length一致,但是也要考虑主机内存的情况。