使用Datapump做数据迁移前，都会统计各种类型的对象数目，便于迁移后进行比对。但在一次数据迁移后，发现LOB对象数目对不上。源库版本是12.2.0.1，目标版本是11.2.0.4。开始猜测是版本差异，统计LOB对象有些差异，仔细调查后发现，不是这么回事。
# 普通表不含 LOB 列
SQL> create table t1 (id int, name varchar2(10));
Table created.
SQL> insert into t1 values(1, 'huayd');
1 row created.
SQL> commit;
Commit complete.
# 回收站无对象
SQL> show recyclebin;
SQL> drop table t1;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$d4rf0DuSPdrgUxZvAgpzaw==$0 TABLE 2018-10-06:15:01:32
# table删除后，dba_objects不再该table的纪录
SQL> select OWNER, OBJECT_NAME from dba_objects where OWNER='HUAYD';
no rows selected
# 创建含 LOB 列的对象
SQL> create table t1 (id int, name varchar2(10), col_lob clob);
Table created.
SQL> purge recyclebin;
Recyclebin purged.
SQL>
SQL> insert into t1 values(1, 'huayd', 'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select OWNER, OBJECT_NAME from dba_objects where OWNER='HUAYD';
OWNER OBJECT_NAME
--------------- ------------------------------
HUAYD SYS_IL0000092503C00003$$
HUAYD T1
HUAYD SYS_LOB0000092503C00003$$
SQL> select OWNER, OBJECT_NAME,object_type from dba_objects where OWNER='HUAYD';
OWNER OBJECT_NAME OBJECT_TYPE
--------------- ------------------------------ -------------------
HUAYD SYS_IL0000092503C00003$$ INDEX
HUAYD T1 TABLE
HUAYD SYS_LOB0000092503C00003$$ LOB
SQL>
SQL> drop table t1;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$d4rf0DuTPdrgUxZvAgpzaw==$0 TABLE 2018-10-06:15:06:40
# 含 LOB 列的对象，被drop后，dba_objects里仍保留LOB对象的纪录，这也就是数据导出再导入后，对象数目不一致的地方
SQL> select OWNER, OBJECT_NAME,object_type from dba_objects where OWNER='HUAYD';
OWNER OBJECT_NAME OBJECT_TYPE
--------------- ------------------------------ -------------------
HUAYD SYS_LOB0000092503C00003$$ LOB
SQL>
结论：含LOB的对象被drop后，在dba_objects中仍有纪录，但是使用Datapump进行数据导出时，并不会导出回收站内的对象，因此最后数据导入后，对比对象数目时会有差异。
--End
Mason
网友评论