Oracle 11.2.0.1 Client Export Oracle 11.2.0.2 database Partition section error problem solution for partitioned tables

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

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

Starting with version 11.2.0.1, Oracle offers a new way to allocate space: When you create a segment table, the table is not created immediately, but it is created until the first row of records is inserted, segment, This is not the same as our previous segment creation and spatial allocation methods. Such segments are also calledDelay Segment. Starting with version 11.2.0.2, increased support for partitioned tables and LOB fields This feature is controlled by the initialization parameter deferred_segment_creation, which defaults to true.let's look at an example on a 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 2010 Copyright (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 defer red_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------------TEST1you can see that only TEST1 this table has a corresponding segment.Sql> Select table_name from tabs T where don't exists (select Segment_name from user_segments s where s.segment _name=t.table_name); TABLE_NAME------------------test3test2sql>Test2,test3 did not create the corresponding segment because there were no records in the table created. Here's a look at the traditional exp/imp support for test1,test2,test3 in this case:[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 applicatio n Clusters, Automatic Storage Management, Olap,data Mining and real application testing-do in Oexport us7ascii Set and Al16utf16 NCHAR character Setserver uses ZHS16GBK character set (possible charset conversion) about to export spec ified 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>you can see exp-00011d error prompts because TEST2,TEST3 does not have a corresponding segment.

But in the 11.2.0.2 version, exp can complete this action again.

[Oracle@vmora]/home/oracle> exp study/study tables=test1,test2,test3 file=test.dmp  

Export:release 11.2.0.2.0-production on Wed June 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-production
with the partitioning, OLAP, Data Mining and real application testing options
Export do in Us7ascii character set and Al16utf16 NCHAR character 6/>server uses ZHS16GBK character set (Possible charset)

About to export specified tables via conventional Path ...
. . Exporting table                          TEST1          1 rows exported
. Exporting table                          TEST2          0 rows exported
... exporting tab Le                          TEST3          0 rows exported
Export terminated successfully without warnings.

So how do you create the corresponding segment for these objects in the 11.2.0.1? There are a lot of methods here, I use the direct allocation of a extent method:
[ORACLE@RAC01]/home/oracle> sqlplus study/study@demo sql*plus:release 11.2.0.1.0 Production on Sun Nov 7 19:21:47 2  010 Copyright (c) 1982, 2009, Oracle.


All rights reserved. Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production and the partitioning, real Applicati On clusters, Automatic Storage Management, OLAP, Data Mining and real application testing options sql> select ' Alter t Able ' | | 
     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 selected sql> you can see, now test1,test2,test3 have a corresponding segment, and then look at the traditional Exp/imp  

[ORACLE@RAC01]/home/oracle> exp study/study tables= (TEST1,TEST2,TEST3) file=test.dmp export:release.  1.0-production on the Sun Nov 7 19:22:50 (c) 1982, 2009, Oracle and/or its affiliates.


All rights reserved. Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production and the partitioning, real Applicati On clusters, Automatic Storage Management, OLAP, Data Mining and real application testing o Export do in Us7ascii Charac ter set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) about to Expor T specified tables via conventional Path .... . Exporting table TEST1 1 rows exported. . Exporting table TEST2 0 rows exported. .
Exporting table TEST3 0 rows exported Export terminated successfully without. 
 [ORACLE@RAC01]/home/oracle>It's the same as the results we've been getting. 

Note: EXPDP/IMPDP to deferred segment is supported, so everyone in the 11GR2 and subsequent versions try not to use the traditional exp/imp, the traditional exp/imp in the new version of a lot of problems, this article is no longer introduced, Oracle also does not recommend the use of traditional exp/imp.enhancement of deferred segment in 11.2.0.2Enhancements to the deferred Segment creation in 11.2.0.2, see my previous blog postThis section focuses on the new additions to the Dbms_space_adminMaterialize_deferred_segments drop_empty_segmentstwo processes for an experience[Oracle@vmora]/home/oracle> sqlplus "/as sysdba" Sql*plus:release 11.2.0.2.0 Production on Mon June 11 22:11:56 2011  Copyright (c) 1982, Oracle.

All rights reserved.

Connected to a idle instance.

Sql> Startup ORACLE instance started. Total System Global area 836976640 bytes Fixed size 1347396 bytes Variable Size 213909692 by
TEs database buffers 616562688 bytes Redo buffers 5156864 Database bytes.
Database opened.
Sql> Conn Study/study Connected. 
  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.Create a deferred table 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 t

Able_name = ' T ') 7 order by 1, 2; No rows selectedyou can see that there is no corresponding segment and then I use Materialize_deferred_segments to instantiate the segment of the partitioned tableSql> BEGIN 2 sys.dbms_space_admin.materialize_deferred_segments (3 schema_name => ' STUDY ', 4 tabl
  E_name => ' T ' 5);
  6 end; 7/pl/sql procedure successfully completed.check again.: sql> SELECT segment_name, Segment_type, Bytes, extents 2 from user_segments 3 WHERE segment_name in (' T ', ' t_p  K ') 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 1 sys_lob0000075708c0000          2$$ LOB PARTITION 8388608 1 sys_lob0000075708c00002$$ lob PARTITION 8388608 1 sys_lob0000075708c00002$$ LOB PARTITION 8388608 1 T TABLE parti                                tion 8388608 1 T TABLE PARTITION 8388608 1 t  Table PARTITION 8388608 1 T table PARTITION     8388608 1 t_pk INDEX PARTITION 65536 1 t_pk          Index PARTITION 65536 1 T_PK index PARTITION 65536
1 t_pk INDEX PARTITION 65536 1 rows selected. 
 Sql>the corresponding segment are all created, so I insert a row belowSql> INSERT INTO t values (1, ' a ');
1 row created.
Sql> commit; Commit complete.then call drop_empty_segments to remove the empty segmentSql> 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.Check again .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 t

Able_name = ' T ') 7 order by 1, 2; Segment_name segment_type BYTES Extents-----------------------------------------------                                -----------------------sys_lob0000075708c00002$$ LOB PARTITION 8388608 1 T          TABLE PARTITION 8388608 1 t_pk INDEX PARTITION 65536 1 sql>sure enough, except for the data partition, the other empty segment were dropped off. 

For more information on Deferred Segment, please refer to the official manual.