Exporting XMLType Table in Table mode  (using Oracle exp)

Only possible (as far as I know, did check it yet) via expdp in Oracle 11g. When there is a dependancy to the repository and database version <= 11g this can not be done and or is very difficult. I once investigated if transportable tablespaces could be used regarding XMLType stored data. To see how complex this can become I post it here.

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN01101

Note:

The remainder of this chapter assumes that Data Pump is the import/export utility used. However, the transportable tablespaces feature supports both Data Pump and the original import and export utilities, IMP and EXP, with one caveat: you must use IMP and EXP for tablespaces containing XMLTypes. Refer to Oracle Database Utilities for more information on these utilities and to Oracle XML DB Developer's Guide for more information on XMLTypes.

Limitations on Transportable Tablespace Use

Be aware of the following limitations as you plan to transport tablespaces:

- The source and target database must use the same character set and national character set.

- You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.

- Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.

- Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities, not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).

The following query returns a list of tablespaces that contain XMLTypes:

select distinct p.tablespace_name from dba_tablespaces p,
dba_xml_tables x, dba_users u, all_all_tables t where
t.table_name=x.table_name and t.tablespace_name=p.tablespace_name
and x.owner=u.username

See Oracle XML DB Developer's Guide for information on XMLTypes.

Transporting tablespaces with XMLTypes has the following limitations:

- The target database must have XML DB installed.

- Schemas referenced by XMLType tables cannot be the XML DB standard schemas.

- Schemas referenced by XMLType tables cannot have cyclic dependencies.

- Any row level security on XMLType tables is lost upon import. This is because the access control lists (ACLs) that implement the row level security cannot be imported, as the target database may not have the same set of users as the source database.

- If the schema for a transported XMLType table is not present in the target database, it is imported and registered. If the schema already exists in the target database, an error is returned unless the ignore=y option is set.

Hi,
I also need to export table which contain xmltype. I have successfully exported using expdb ,but it unable to import using impdp. Bellow is the error when i try to import using pump.

h4. KUP-11007: conversion error loading table "CC_DBA"."XXXX"
h4. ORA-01403: no data found
h4. ORA-31693: Table data object "XXX_DBA"."XXXX" failed to load/unload and is being skipped due to error:

I have add details description under this export and import XMLType table ,Please help to resolve.