ORA-29341错误

Problem Description:
====================

When trying to export a single partition of a partitioned table with
the transportable tablespace syntax, an ORA-29341 will be returned.

  ORA-29341: The transportable set is not self-contained
      Cause: The set of objects in the set of tablespaces selected are not
             self-contained.
     Action: Consider using different export options, or removing some of the
             pointers that caused the violation, or selecting a different set
             of tablespaces.


Solution Description:
=====================

To export a single partition of a multiple partitioned table stored in a separate
tablespace with the export syntax transport_tablespace=y,
the following must be done:

1. Set the tablespace in read only mode (necessary to export tablespace
   with transport_tablespace=y).

SQL> alter tablespace mytbs read only;


2. Create a table with the same columns as the partitioned table(oldtable)
   in another tablespace.

SQL> create table mytable tablespace othertbs
     as select * from oldtable where 1 = 2 ;

Note: if the table contains a varray stored as a LOB or LOB index (in each partition), you will receive an "ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" or an "ORA-14099: all rows in table do not qualify for specified partition."
To work around this, create the new table with (1) a new name, (2) new tablespace name, and (3) the same DDL as the source-partitioned table, but omit the partition definition(s).


3.  Exchange the partition with the newly created table.

SQL> alter table oldtable exchange partition p1 with table mytable ;
 

4.  Export the metadata of the tablespace.

$ exp user/pwd transport_tablespace=y tablespaces=mytbs file=my_exp_file.dmp


5. Set the Tablespace in read write mode if necessary.

SQL> alter tablespace mytbs read write;

-----------------
IMPORTANT REMARK:
-----------------

When a partition owns a local index partition, when exchanging the partition
into a table, INCLUDE the INDEXES .
Testcase:
SQL> connect scott/tiger
Connected.
SQL> CREATE TABLE TACT
  2      (no NUMBER NOT NULL,
  3       CAIEXE_ACT date)
  4  PARTITION BY RANGE (CAIEXE_ACT)
  5  (
  6   PARTITION PTACT_M199905 VALUES LESS THAN
  7             (TO_DATE(' 1999-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_
CALENDAR=GREGORIAN'))
  8             tablespace TS_RM_OSI_T199905_D,
  9   PARTITION PTACT_M199906 VALUES LESS THAN
10             (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_
CALENDAR=GREGORIAN'))
11             tablespace TS_RM_OSI_T199906_D,
12   PARTITION PTACT_M199907 VALUES LESS THAN
13             (TO_DATE(' 1999-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_
CALENDAR=GREGORIAN'))
14             tablespace TS_RM_OSI_T199907_D);
Table created.
SQL>
SQL> alter session set nls_date_format='SYYYY-MM-DD HH24:MI:SS';
Session altered.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-04-01 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-04-11 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-04-21 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-05-01 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-05-28 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-05-30 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-06-01 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-06-11 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-06-21 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-07-01 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-07-21 00:00:00');
1 row created.
SQL>
SQL> insert into TACT
  2  values (1, ' 1999-07-29 00:00:00');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> CREATE BITMAP INDEX I_CAIEXE_ACT ON TACT (CAIEXE_ACT) LOCAL (
  2   PARTITION PCAIEXE_ACT_M199905 TABLESPACE TS_RM_OSI_T199905_I,
  3   PARTITION PCAIEXE_ACT_M199906 TABLESPACE TS_RM_OSI_T199906_I,
  4   PARTITION PCAIEXE_ACT_M199907 TABLESPACE TS_RM_OSI_T199907_I);
Index created.
SQL>
SQL> create table PTACT_M199907 tablespace TS_mnxmn0605 as
select * from TACT where rownum < 1;
Table created.
SQL>
SQL> create BITMAP index ITACT_M199907 on PTACT_M199907 (CAIEXE_ACT)
tablespace TS_mnxmn0605;
Index created.
SQL>
SQL> alter table TACT exchange partition PTACT_M199907 with table
  2  PTACT_M199907  INCLUDING INDEXES WITH VALIDATION;
Table altered.
SQL>
SQL> select segment_name, segment_type
  2  from user_segments
  3  where tablespace_name in ('TS_RM_OSI_T199907_D','TS_RM_OSI_T199907_I'); 

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
ITACT_M199907
INDEX

PTACT_M199907
TABLE
SQL>
SQL> execute sys.dbms_tts.transport_set_check
('TS_RM_OSI_T199907_D,TS_RM_OSI_T199907_I',FALSE);
PL/SQL procedure successfully completed.
SQL>
SQL> select * from sys.transport_set_violations where rownum < 10;
no rows selected
SQL>
SQL> alter tablespace TS_RM_OSI_T199907_D read only;
Tablespace altered.
SQL>
SQL> alter tablespace TS_RM_OSI_T199907_I read only;
Tablespace altered.
SQL> !
$ exp FILE=/tmp/RM_PARTIEL TRANSPORT_TABLESPACE=Y
TABLESPACES=\(TS_RM_OSI_T199907_D,TS_RM_OSI_T199907_I\)
Export: Release 8.1.7.2.0 - Production on Wed Dec 19 15:39:55 2001
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Username: / as sysdba
Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.2.0 - Production
Export done in WE8ISO8859P1 character set and US7ASCII NCHAR character set
server uses US7ASCII character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TS_RM_OSI_T199907_D ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                  PTACT_M199907
For tablespace TS_RM_OSI_T199907_I ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
$



Explanation:
============

It is not possible to export the tablespace because all the objects
are not fully contained inside the single tablespace. Parts of them are stored
in another tablespace. This is the case for a particular partition of
a partitioned table.

The tablespace to be exported must contain all or none partitions of a
partitioned table.

In this case, if it is necessary to transport a subset of a partitioned table,
the partitions must be exchanged.

Please refer to ORACLE8 Administrator's guide, chapter "managing tablespaces"
for complete information about transportable tablespaces functionality
and restrictions.

If the source partitioned table contains a varrary stored as a LOB or LOB index, see the note on Step #2 above for creating the new exchange table.


Search Words:
=============

self-contained, dbms_tts.transport_set_check, transport_set_violations,
transport_tablespace, ora-29341


References:
===========

Transportable Tablespace Fails on Export