利用 rowid 分片导出导入 lob 大表及数据泵最佳实践

前 言

有些时候为了缩短数据泵导入导出的时间，一般会选择将大表和其他表分开导出，尤其是遇到大表有 lob 字段的，导出时间会更慢。这样则需要分开导出大表和其他表，而单独导出大表也会很慢，可以利用 rowid 分片技术将大表分开来同时导出，提高导出效率。

1、大表信息

表记录只有 1525536 条，但是只有 1.8G 大小，有两个 CLOB 字段，查看 Lob 大小有 13.71G 大小。

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> select count(*) from PROD.T_CA_TX_HIS; COUNT(*) ---------- 1525536 SQL> set line 120 SQL> col OWNER for a30 SQL> col SEGMENT_NAME for a30 SQL> select OWNER,SEGMENT_NAME,BYTES/1024/1024/1024 from dba_segments where SEGMENT_NAME='T_CA_TX_HIS' and OWNER='PROD'; OWNER SEGMENT_NAME BYTES/1024/1024/1024 ------------------------------ ------------------------------ -------------------- PROD T_CA_TX_HIS 1.83007813 SQL> desc PROD.T_CA_TX_HIS Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- SEQUENCE_NO NOT NULL NUMBER(18) PK_TX_HIS NOT NULL VARCHAR2(36) FK_USER_CCBSCF VARCHAR2(36) FK_PERSON_CCBSCF VARCHAR2(36) SOFT_CA_BRAND NOT NULL VARCHAR2(20) SOFT_CA_USER_ID VARCHAR2(50) TARGET_URL VARCHAR2(300) TX_CODE VARCHAR2(20) TX_REQUEST CLOB TX_RESPONSE CLOB TX_ERROR_CODE VARCHAR2(50) TX_ERROR_MESSAGE VARCHAR2(500) CREATE_TIME NOT NULL DATE FK_USER_CREATE NOT NULL VARCHAR2(36) USER_NAME_CREATE NOT NULL VARCHAR2(60) SQL> col SEGMENT_NAME for a30 SQL> select segment_name,BYTES/1024/1024/1024 from dba_segments where OWNER='PROD' group by segment_name,BYTES/1024/1024/1024 order by 2 asc; .........省略........ SEGMENT_NAME BYTES/1024/1024/1024 ------------------------------ -------------------- T_OPEN_MESSAGE_SMS_RECORD 2.8125 SYS_LOB0000167075C00010$$ 13.7119141 --查看某用户下 Lob 字段大小 SET LINE 345 PAGES 456 COL OWNER FOR a20 COL TABLE_NAME FOR A40 COL SEGMENT_TYPE FOR A20 col COLUMN_NAME FOR A35 SELECT A.OWNER, B.TABLE_NAME, B.COLUMN_NAME, a.SEGMENT_TYPE, ROUND(SUM(A.BYTES/1024/1024/1024),2) G FROM DBA_SEGMENTS A LEFT JOIN DBA_LOBS B ON A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.SEGMENT_NAME WHERE A.SEGMENT_TYPE='LOBSEGMENT' AND A.OWNER in('&OWNER') GROUP BY A.OWNER,B.TABLE_NAME,B.COLUMN_NAME,a.SEGMENT_TYPE ORDER BY 5 DESC; OWNER TABLE_NAME COLUMN_NAME SEGMENT_TYPE G -------------------- ---------------------------------------- ----------------------------------- -------------------- ---------- PROD T_CA_TX_HIS TX_RESPONSE LOBSEGMENT 13.71