Solution to partition table partition error when Oracle 11.2.0.1 client exports Oracle 11.2.0.2 Database

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

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

 

Since version 11.2.0.1, Oracle has provided a new space allocation method: When you create a non-partition table, this table segment is not created immediately, instead, the segment is created only when the first row of the record is inserted. This is different from the method used to create and allocate space for the segment. such a segment is also calledLatency. Support for partition tables and LOB fields has been added since version 11.2.0.2. This feature is controlled by the initialization parameter deferred_segment_creation. The default value of this parameter is true.Let's take a look at an example in 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,200 9, 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------------TEST1We can see that only the table test1 has a corresponding 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 and test3 do not create the corresponding segment, because there are no records in the table during creation. Let's take a look at the support of the traditional exp/imp for test1, Test2, and 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 2010 copyright (c) 1982,200 9, 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 character set (possible charset conversion) about to export ifspecied
Tables via conventional path ..... exporting table test1 rows exportedEXP-00011: study. test2 does not existEXP-00011: study. test3 does not existexport terminated successfully with warnings. [Oracle @ rac01]/home/Oracle>
We can see that because Test2, test3 does not have the corresponding segment, and a EXP-00011d error message is displayed.

But in 11.2.0.2, exp can do this 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 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> 
In 11.2.0.1, what is the corresponding segment created for these objects? There are many methods. Here I use the method of allocating an extent directly:[Oracle @ rac01]/home/Oracle> sqlplus study/Study @ demosql * Plus: Release 11.2.0.1.0 production on Sun Nov 7 19:21:47 2010 copyright (c) 1982,200 9, 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 64 K ); '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 64 K ); alter table Test2 allocate extent (size 64 K); SQL> ALTER TABLE test3 allocate extent (size 64 K); table altered. SQL> ALTER TABLE Test2 allocate extent (size 64 K); 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>We can see that test1, Test2, and test3 all have corresponding segment. Let's look at the traditional 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 2010 copyright (c) 1982,200 9, 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 convert nchar character setserver uses character set (possible charset conversion) about to export specified tables via conventional path ..... exporting table test1 rows exported .. exporting table Test2 0 rows exported .. exporting table test3 0 rows exportedexport terminated successfully without warnings. [Oracle @ rac01]/home/Oracle>The result is the same as the previous one.
Note: Expdp/impdp supports deferred segment. Therefore, do not use the traditional exp/IMP in 11gr2 and later versions, the traditional exp/IMP has a lot of problems in the new version. This article will not introduce it any more. Oracle officially does not recommend that you use the traditional exp/imp.Deferred segment enhancement in 11.2.0.2In 11.2.0.2, deferred segment creation is enhanced. For more information, see my previous blog posts.This section focuses on the newly addedMaterialize_deferred_segments drop_empty_segmentsOne experience in two processes[Oracle @ vmora]/home/Oracle> sqlplus "/As sysdba" SQL * Plus: Release 11.2.0.2.0 production on Mon Jul 11 22:11:56 2011 copyright (c) 1982,201 0, 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.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 table_name = 'T') 7 order by 1, 2; No rows selectedWe can see that there is no corresponding segment. Then, I use materialize_deferred_segments to instantiate the segment of the partition table.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.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 table_name = 'T') 7 order by 1, 2; segment_name segment_type bytes extents partial partition ------------ partial $ lob partition 8388608 partial $ lob partition 8388608 partial $ lob partition 8388608 partial $ lob partition 8388608 1 t table partition 8388608 1 t table partition 8388608 1 t table partition 8388608 1 t table partition 8388608 1t_pk index partition 65536 1t_pk index partition 65536 1t_pk index partition 65536 1t_pk index partition 65536 rows selected. SQL>The corresponding segment has been created. Next I will insert a row of records.SQL> insert into T values (1, 'A'); 1 row created. SQL> commit; Commit complete.Then, call drop_empty_segments to delete the empty 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.Check againSQL> 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 partitions ---------------- ---------- ------------ sys_lob0000075708c00002 $ lob partition 8388608 1 t table partition 8388608 1t_pk index partition 65536 1sql>Sure enough, except for partitions with data, all other empty segments are dropped.
For more information about the deferred segment, see the official manual.