exp/imp方式迁移数据库对象不一致问题分析

昨天做了数据库的迁移，由于数据量比较小，采用exp/imp的方式，库迁移的过程中没有报任何错误，但是在迁移完成后两库的对象数据不一致，查询如下：

 

在原库上查询：

SQL> select count(OBJECT_NAME),OBJECT_TYPE from user_objects group by OBJECT_TYPE;

COUNT(OBJECT_NAME) OBJECT_TYPE
------------------ ---------------------------------------------------------
              1631 LOB
              1644 TABLE
              1653 INDEX

 

在新库上查询：

SQL> select count(OBJECT_NAME),OBJECT_TYPE from user_objects group by OBJECT_TYPE;
 
COUNT(OBJECT_NAME) OBJECT_TYPE
------------------ -------------------
              1621 LOB
              1653 INDEX
              1644 TABLE

 

可见，LOB对象数不一致，继续查user_lobs视图，如下：

 

原库上查询：

select count(*) from user_lobs;
1621

 

新库上查询：

select count(*) from user_lobs;
1621

 

很奇怪，为什么user_lobs视图查询结果一致，而 user_objects 查询结果不一致

 

继续查询未迁移过来的LOB对象：

 

在原库上查询：

 

select b.*
from user_segments b
where b.segment_name  in ( 
select a.OBJECT_NAME
from user_objects a
where a.OBJECT_NAME not in (select segment_name from user_lobs)
  and a.OBJECT_TYPE = 'LOB');

 

1 SYS_LOB0000076835C00003$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
2 SYS_LOB0000087181C00004$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
3 SYS_LOB0000076839C00004$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
4 SYS_LOB0000094418C00003$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
5 SYS_LOB0000087177C00003$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
6 SYS_LOB0000087173C00003$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
7 SYS_LOB0000087191C00004$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
8 SYS_LOB0000094422C00004$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
9 SYS_LOB0000095266C00003$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT
10 SYS_LOB0000095270C00004$$  LOBSEGMENT ASSM ROADSHOW 65536 8 1 65536 1048576 1 2147483645 2147483645      DEFAULT

 

正好10个对象，是ASSM ，怀疑是空段，迁移时数据库自动忽略了，进一步查询，这些段分配的数据块：

 

select block_id, file_id, segment_name, blocks, extent_id
  from dba_extents
 where wner = 'ROADSHOW'
   and segment_name in ('SYS_LOB0000076835C00003$$',
                        'SYS_LOB0000087181C00004$$',
                        'SYS_LOB0000076839C00004$$',
                        'SYS_LOB0000094418C00003$$',
                        'SYS_LOB0000087177C00003$$',
                        'SYS_LOB0000087173C00003$$',
                        'SYS_LOB0000087191C00004$$',
                        'SYS_LOB0000094422C00004$$',
                        'SYS_LOB0000095266C00003$$',
                        'SYS_LOB0000095270C00004$$')
 order by extent_id;

查询结果为空，得出结论：数据库在导出时自动忽略了没有分配数据块的LOBSEGMENT。

 

补充：

1、数据泵的方式也存在上述问题。

2、ASSM请参考：

http://blog.mchz.com.cn/?p=2060

和

http://www.eygle.com/archives/2007/07/oracle_assm_level3_bmb.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27471208/viewspace-750039/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27471208/viewspace-750039/