Oracle 11.2.0.1客户端导出Oracle 11.2.0.2数据库时有分区表的分区段错误问题解决方案

http://www.itpub.net/thread-1478468-1-1.html

http://tomszrp.itpub.net/post/11835/520574

 

从11.2.0.1版本开始，Oracle又提供了一种新的空间分配方法:当你Create一个非分区表时,这个Table Segment并没有立刻创建,而是直到有第一行记录插入的时候才去创建这个Segment,这和我们以前的Segment的创建和空间分配方法是不一样.这样的段也被称为延迟段.从11.2.0.2版本开始，增加了对分区表和LOB字段的支持这个特性由初始化参数deferred_segment_creation来控制,该参数默认是true.下面先看一个11.2.0.1上的例子[oracle@rac01] /home/oracle> sqlplus study/study@demoSQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 19:13:19 2010Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL>show parameter deferred_segment_creationNAME                                 TYPE        VALUE------------------------------------ ----------- ------deferred_segment_creation            boolean     TRUESQL> SQL>create table test1 as select * from dual;Table created.SQL>create table test2 as select * from dual where 0=1;Table created.SQL>create table test3 as select * from dual where 0=1;Table created.SQL>select table_name from tabs;TABLE_NAME---------------TEST1TEST2TEST3SQL>select segment_name from user_segments;SEGMENT_NAME------------TEST1可以看到只有TEST1这个表有对应的segment.SQL>select table_name      from tabs t      where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);TABLE_NAME------------------TEST3TEST2SQL> 而TEST2,TEST3没有创建对应的segment,因为创建时表里没有任何记录。下面看看传统的exp/imp对这种情况下的test1,test2,test3的支持：[oracle@rac01] /home/oracle>exp study/study tables=(test1,test2,test3) file=test.dmp           Export: Release 11.2.0.1.0 - Production on Sun Nov 7 19:19:37 2010Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion)About to export specified tables via Conventional Path .... . exporting table                          TEST1          1 rows exportedEXP-00011: STUDY.TEST2 does not existEXP-00011: STUDY.TEST3 does not existExport terminated successfully with warnings.[oracle@rac01] /home/oracle>可以看到因为test2,test3没有对应的segment,而出现EXP-00011d的错误提示。

但在11.2.0.2版本中，exp又可以完成这个动作了
[oracle@vmora] /home/oracle> exp study/study tables=test1,test2,test3 file=test.dmp  
Export: Release 11.2.0.2.0 - Production on Wed Jul 13 23:33:18 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path .... . exporting table                          TEST1          1 rows exported. . exporting table                          TEST2          0 rows exported. . exporting table                          TEST3          0 rows exportedExport terminated successfully without warnings.[oracle@vmora] /home/oracle> 
那么在11.2.0.1中如何为这些对象创建对应的segment呢？方法有很多， 我这里采用直接分配一个extent的方法：[oracle@rac01] /home/oracle> sqlplus study/study@demoSQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 19:21:47 2010Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> select 'alter table '||table_name||' allocate extent(size 64k);'      from tabs t      where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT(SIZE64K);'---------------------------------------------------------------------alter table TEST3 allocate extent(size 64k);alter table TEST2 allocate extent(size 64k);SQL> alter table TEST3 allocate extent(size 64k);Table altered.SQL> alter table TEST2 allocate extent(size 64k);Table altered.SQL> select table_name      from tabs t      where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);no rows selectedSQL> 可以看到，现在test1,test2,test3都有对应的segment了，下面再看看传统的exp/imp[oracle@rac01] /home/oracle> exp study/study tables=(test1,test2,test3) file=test.dmp       Export: Release 11.2.0.1.0 - Production on Sun Nov 7 19:22:50 2010Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing oExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion)About to export specified tables via Conventional Path .... . exporting table                          TEST1          1 rows exported. . exporting table                          TEST2          0 rows exported. . exporting table                          TEST3          0 rows exportedExport terminated successfully without warnings.[oracle@rac01] /home/oracle> 和我们以前得到的结果是一样的了。
注：expdp/impdp对deferred segment是支持的，所以大家在11gr2及后续的版本中尽量不要使用传统的exp/imp,    传统的exp/imp在新版本中很有很多的问题，本文不再介绍，Oracle官方也不建议大家使用传统的exp/imp.11.2.0.2中deferred segment的增强11.2.0.2中对Deferred Segment Creation的增强,参见我之前的博文    本节中主要针对DBMS_SPACE_ADMIN中新增加的    MATERIALIZE_DEFERRED_SEGMENTS    DROP_EMPTY_SEGMENTS两个过程进行一个体验[oracle@vmora] /home/oracle> sqlplus "/as sysdba"SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 11 22:11:56 2011Copyright (c) 1982, 2010, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  836976640 bytesFixed Size                  1347396 bytesVariable Size             213909692 bytesDatabase Buffers          616562688 bytesRedo Buffers                5156864 bytesDatabase mounted.Database opened.SQL> conn study/studyConnected.SQL> CREATE TABLE t (  2    id NUMBER,  3    c CLOB,  4    CONSTRAINT t_pk PRIMARY KEY (id) USING INDEX LOCAL  5  )  6  SEGMENT CREATION DEFERRED  7  PARTITION BY HASH(id) PARTITIONS 4;Table created.创建一个deferred的表tSQL> SELECT segment_name, segment_type, bytes, extents  2   FROM user_segments  3   WHERE segment_name IN ('T','T_PK')  4   OR segment_name IN (SELECT segment_name  5                       FROM user_lobs  6                       WHERE table_name = 'T')  7   ORDER BY 1,2;no rows selected可以看到确实没有对应的segment然后我用materialize_deferred_segments对该分区表的segment进行实例化SQL> BEGIN  2    sys.dbms_space_admin.materialize_deferred_segments(  3      schema_name => 'STUDY',  4      table_name  => 'T'  5    );  6  END;  7  /PL/SQL procedure successfully completed.再去检查一下：SQL> SELECT segment_name, segment_type, bytes, extents  2   FROM user_segments  3   WHERE segment_name IN ('T','T_PK')  4   OR segment_name IN (SELECT segment_name  5                       FROM user_lobs  6                       WHERE table_name = 'T')  7   ORDER BY 1,2;SEGMENT_NAME                     SEGMENT_TYPE            BYTES    EXTENTS-------------------------------- ------------------ ---------- ----------SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1T                                TABLE PARTITION       8388608          1T                                TABLE PARTITION       8388608          1T                                TABLE PARTITION       8388608          1T                                TABLE PARTITION       8388608          1T_PK                             INDEX PARTITION         65536          1T_PK                             INDEX PARTITION         65536          1T_PK                             INDEX PARTITION         65536          1T_PK                             INDEX PARTITION         65536          112 rows selected.SQL> 对应的segment都创建好了，下面我插入一行记录SQL> insert into t values(1,'a');1 row created.SQL> commit;Commit complete.然后调用drop_empty_segments将空的segment删除SQL> BEGIN  2    dbms_space_admin.drop_empty_segments(  3      schema_name => 'STUDY',  4      table_name  => 'T'  5    );  6  END;  7  /PL/SQL procedure successfully completed.再检查一下SQL> SELECT segment_name, segment_type, bytes, extents  2   FROM user_segments  3   WHERE segment_name IN ('T','T_PK')  4   OR segment_name IN (SELECT segment_name  5                       FROM user_lobs  6                       WHERE table_name = 'T')  7   ORDER BY 1,2;SEGMENT_NAME                     SEGMENT_TYPE            BYTES    EXTENTS-------------------------------- ------------------ ---------- ----------SYS_LOB0000075708C00002$$        LOB PARTITION         8388608          1T                                TABLE PARTITION       8388608          1T_PK                             INDEX PARTITION         65536          1SQL>     果然，除了有数据的分区，其他的空segment都被drop掉了。
更多关于 Deferred Segment ，请参阅官方手册。