36 Exporting and Importing XMLType Tables

This chapter describes how you can export and import XMLType tables for use with Oracle XML DB using Oracle Data Pump.

Note:

The original Export and Import utilities can be used to migrate data to database releases that are prior to Oracle Database 11g Release 2 (11.2).

This chapter discusses the following topics:

Overview of Oracle Data Pump
EXPORT/IMPORT Support in Oracle XML DB
Exporting XML Schema-Based XMLType Tables
Exporting Hierarchy-Enabled (Repository) Tables
Exporting and Importing Transportable Tablespaces
Repository Resources and Foldering Support
Export/Import Syntax and Examples

Overview of Oracle Data Pump

Oracle Data Pump technology enables high-speed movement of data and metadata from one database to another. Oracle Data Pump has two command-line clients, expdp and impdp, that invoke Data Pump Export utility and Data Pump Import utility, respectively. The expdp and impdp clients use procedures provided in PL/SQL package DBMS_DATAPUMP to execute export and import commands, passing the parameters entered at the command-line. These parameters enable the exporting and importing of data and metadata for a complete database or subsets of a database.

The Data Pump Export and Import utilities (invoked with commands expdp and impdp, respectively) have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate.

EXPORT/IMPORT Support in Oracle XML DB

Oracle XML DB supports export and import of XMLType tables and columns that store XML data.

You can export and import this data regardless of the XMLType storage format (structured, unstructured, or binary XML). However, Data Pump exports and imports XML data as text or binary XML data only. The underlying object-relational tables and columns used for structured storage of XMLType are thus not exported. Instead, they are converted to binary form and then exported as self-describing binary XML data. XMLType data stored as CLOB instances (unstructured storage) is exported as text.

Note:

Oracle Data Pump for Oracle Database 11g Release 1 (11.1) does not support the export of XML schemas, XML schema-based XMLType columns, or binary XML data to database releases prior to 11.1.

Regardless of the export format, the format of the dump file is either CLOB or self-describing binary XML with a token map preamble. How Oracle Data Pump stores this data in the dump file depends on the value of the export parameter, data_options (the only valid value for this parameter is xml_clobs.) If you specify this value on the export command line, all XMLType data is stored in text format in the dump file. If you do not specify the xml_clobs parameter in the expdp command, then the format of the XMLType columns in the table determines the format of the data in the dump file. Table 36-1 shows the format of the XMLType columns in the table with the corresponding format of the dump file.

Table 36-1 Format of the XMLType columns in the table with the corresponding format of the dump file

Storage Model of XMLType Columns	Dump File Format of XML Data
Unstructured storage	Text
Structured storage, binary XML storage, or hybrid storage (a mixture of unstructured and structured storage)	Self-describing binary XML

Since XMLType data is exported and imported as XML data, the source and target databases can use different XMLType storage models for that data. You can export data from a database that stores XMLType data one way and import it into a database that stores XMLType data a different way.

Note:

When importing using an external table, do not use option append to import more than once from the same dump file into an XMLType table or column, regardless of the XMLType storage model used. Doing so raises a unique-constraint violation error.

See Oracle Database Utilities for information about importing using an external table.

Exporting XML Schema-Based XMLType Tables

You can export XMLType tables, whether they are XML schema-based or not. If a table is XML schema-based, then it depends on the XML schema used to define its data. This XML schema can also have dependencies on SQL object types that are used to store the data, in the case of structured storage. Therefore, exporting a user who has XML schema-based XMLType tables also exports the following:

SQL objects types (if structured storage was used)
XML schemas
XML tables

Exporting Hierarchy-Enabled (Repository) Tables

The following guidelines apply to exporting hierarchy-enabled tables, that is, tables that underlie Oracle XML DB Repository data:

The row-level security (RLS) policies and path-index triggers are not exported for hierarchy-enabled tables. When these tables are imported, they are not hierarchy-enabled.
Hidden columns ACLOID and OWNERID are not exported for these tables. In an imported database the values of these columns could be different, so they should be reinitialized.

Exporting and Importing Transportable Tablespaces

Using the transportable tablespace feature, you can move a set of tablespaces from one Oracle database to another, whether it is XML schema-based or non-schema-based. When you export using transportable tablespaces mode, only the metadata for tables (and their dependent objects) within a specified set of tablespaces are unloaded. You can then copy the tablespace data files to another Oracle database and perform a transport tablespace import. This is generally very fast, because it involves only copying the tablespace and re-creating the tablespace metadata.

Use TRANSPORT_TABLESPACES parameter in expdp to specify a list of tablespace names for which the object metadata is to be exported from the source database to the target database.

You cannot export transportable tablespaces and import them to a database at a lower release level. The target database must be at the same or higher release level as the source database.

When exporting, Oracle XML DB Repository hierarchy information is lost (see "Exporting Hierarchy-Enabled (Repository) Tables"). When importing, any XML schemas referenced by the data to be imported are also imported.

Repository Resources and Foldering Support

Oracle XML DB supports a foldering mechanism that helps store content in the database in hierarchical structures, as opposed to traditional relational database structures. Foldering lets you use path names and URIs to refer to data (repository resources), rather than table names, column names, and so on. This foldering mechanism is not entirely supported using expdp or impdp.

However, for resources based on a registered XML schema, the XMLType tables storing the data can be exported and imported. During export, only the XML data is exported, the relationship in the Oracle XML DB foldering hierarchy is lost.

Full Database Export

Oracle XML DB stores the metadata (and data unrelated to XML Schema) for Oracle XML DB Repository in database schema (user account) XDB. Because Oracle Database does not export the repository structure, these metadata tables and structures are not exported during a full database export.The entire database schema (user) XDB is skipped during a full database export, and any database objects owned by user XDB are not exported.

Exporting and Importing with Different Character Sets

As with other database objects, XML data is exported in the character set of the exporting server. During import, the data gets converted to the character set of the importing server.

Export/Import Syntax and Examples

Export and import using Oracle Data Pump is described in Oracle Database Utilities. This section includes additional guidelines and examples for using commands expdp and impdp with XMLType data. For tables with XMLType data stored as CLOB, Oracle Data Pump exports and imports the tables in the same way as it would do for any table.

The examples presented here use the command-line commands expdp and impdp. After submitting such a command with a user name and command parameters, you are prompted for a password. The examples here do not show this prompting.

Performing a Table-Mode Export /Import

An XMLType table has a dependency on the XML schema that was used to define it. Similarly, the XML schema has dependencies on the SQL object types created or specified for it. Importing an XMLType table requires the existence of the XML schema and the SQL object types. When a TABLE mode export is used, only the table related metadata and data are exported. To be able to import this data successfully, the user needs to ensure that both the XML schema and object types have been created.

The examples here assume that you are using a database with the following features:

A database with a sample schema
A table foo with an XMLType column in unstructured (CLOB) storage format
A directory object dpump_dir, for which READ and WRITE privileges have been granted to the sample schema

Example 36-1 shows a table-mode export, specified using the TABLES parameter. It exports table foo to foo.dmp dump file.

Example 36-1 Exporting XMLType Data in TABLE Mode

expdp system directory=dpump_dir dumpfile=foo.dmp tables=foo

Note:

In table mode, if you do not specify a schema prefix in the expdp command, the schema of the exporter is used by default.

Example 36-2 shows a table-mode import. It imports table foo from the foo.dmp dump file.

Example 36-2 Importing XMLType Data in TABLE Mode

impdp system tables=foo directory=dpump_dir dumpfile=foo.dmp table_exists_action=append

If a table by the name foo already exists at the time of this import, then parameter table_exists_action appends rows at the end of the existing table. When you use APPEND, the data is always loaded into new space. Existing space, even if available, is never reused. For this reason, you might need to compress your data after the load.

Performing a Schema-Mode Export/Import

When performing a Schema mode export, if you have role EXP_FULL_DATABASE, then you can export a database schema, the database schema definition, and the system grants and privileges of that database schema.

The example here assumes that you are using a database with the following features:

User x4a has created a table po2.
User x4a has a registered XML schema, ipo, which created two ordered collection tables item_oct2 and sitem_nt2.

User x4a creates table po2 as shown in Example 36-3.

Example 36-3 Creating Table po2

CREATE TABLE po2 (po XMLType)
  XMLTYPE COLUMN po
  XMLSCHEMA "ipo.xsd"
  ELEMENT "purchaseOrder"
  VARRAY po.XMLDATA."items"."item"
    STORE AS TABLE item_oct2 ((PRIMARY KEY(NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
      NESTED TABLE po.XMLDATA."shippedItems"."item" STORE AS sitem_nt2;

Table po2 is then populated and exported, as shown in Example 36-4.

Example 36-4 Exporting XMLType Data in SCHEMA Mode

expdp x4a directory=tkxm_xmldir dumpfile=x4a.dmp

Example 36-4 exports all of the following:

All data types that were generated during registration of XML schema ipo.
XML schema ipo.
Table po2 and the ordered collection tables item_oct2 and sitem_nt2, which were generated during registration of XML schema ipo.
All data in all of those tables.

Example 36-5 Importing XMLType Data in SCHEMA Mode

impdp x4a directory=tkxm_xmldir dumpfile=x4a.dmp

Example 36-5 imports all of the data in x4a.dmp to another database, in which the user x4a already exists.

Example 36-6 does the same thing as Example 36-5, but it also remaps the database schema from user x4a to user quine.

Example 36-6 Importing XMLType Data in SCHEMA Mode, Remapping Schema

impdp x4a directory=tkxm_xmldir dumpfile=x4a.dmp remap_schema=x4a:quine

Example 36-6 imports all of the data in x4a.dmp (exported from the database schema of user x4a) into database schema quine. To remap the database schema, user x4a must have been granted role IMP_FULL_DATABASE on the local database and role EXP_FULL_DATABASE on the source database. REMAP_SCHEMA loads all of the objects from the source schema into the target schema.