|Oracle® XML DB Developer's Guide
12c Release 1 (12.1)
|PDF · Mobi · ePub|
This chapter describes how you can use Oracle Data Pump to export and import
XMLType tables for use with Oracle XML DB.
Note:You can use the older export and import utilities
impto migrate data to database releases that are prior to Oracle Database 11g. However, these older utilities do not support using
XMLTypedata that is stored as binary XML.
This chapter discusses the following topics:
Oracle Data Pump enables high-speed movement of data and metadata from one database to another. There are two modes for using Oracle Data Pump: transportable tablespaces mode and non-transportable tablespaces mode. For the transportable tablespaces mode there is this restriction regarding
XMLType data: you cannot change the
XMLType storage model.
As with other database objects, XML data is exported in the character set of the exporting server. During import, the data is converted to the character set of the importing server.
Oracle Data Pump has two command-line clients,
impdp, that invoke Data Pump Export utility and Data Pump Import utility, respectively. The
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
impdp, respectively) have a similar look and feel to the original Export (
exp) and Import (
imp) utilities, but they are completely separate.
Data Pump Export utility (invoked with
expdp) unloads data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility (invoked using
Oracle XML DB supports export and import of
XMLType tables and columns that store XML data, whether it is 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 object-relational storage. Therefore, exporting a user who has XML schema-based
XMLType tables also exports the following:
SQL objects types (if object-relational storage was used)
You can export and import this data regardless of the
XMLType storage format (object-relational or binary XML). However, Oracle Data Pump exports and imports XML data as text or binary XML data only. The underlying tables and columns used for object-relational storage of
XMLType are thus not exported. Instead, they are converted to binary form and then exported as self-describing binary XML data.
Note:Oracle Data Pump for Oracle Database 11g Release 1 (11.1) does not support the export of XML schemas, XML schema-based
XMLTypecolumns, or binary XML data to database releases prior to 11.1.
Regardless of the
XMLType storage model, the format of the dump file is either text or self-describing binary XML with a token map preamble. By default, self-describing binary XML is used.
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 then all
XMLType data is stored in text format in the dump file. Otherwise, the dump file uses binary XML.
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:Do not use option
table_exists_action=appendto import more than once from the same dump file into an
XMLTypetable, regardless of the
XMLTypestorage model used. Doing so raises a unique-constraint violation error because rows in
XMLTypetables are always exported and imported using a unique object identifier.
See Oracle Database Utilities for information about
You can export and import the
XMLType tables that store the XML data for Oracle XML DB Repository resources that are based on a registered XML schema.
However, only the XML data is exported. The repository structure, that is, the relationships in the foldering hierarchy, are lost during export. 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.
Export and import using Oracle Data Pump is described in Oracle Database Utilities. This section includes additional guidelines and examples for using commands
The examples presented here use the command-line commands
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.
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 schema
user23.tab41 with an
XMLType column stored as binary XML
A directory object
dpump_dir, for which
WRITE privileges have been granted to the user running
Example 36-1 shows a table-mode export, specified using the
TABLES parameter. It exports table
tab41 to dump file
expdp system directory=dpump_dir dumpfile=tab41.dmp tables=user23.tab41
Note:In table mode, if you do not specify a schema prefix in the
expdpcommand then the schema of the exporter is used by default.
Example 36-2 shows a table-mode import. It imports table
tab41 from dump file
impdp system tables=user23.tab41 directory=dpump_dir dumpfile=tab41.dmp table_exists_action=append
If a table named
tab41 already exists at the time of the import then specifying
table_exists_action = append causes rows to be appended to that table. Whenever you use parameter value
append the data is loaded into new space; existing space is never reused. For this reason you might need to compress your data after the load operation.
See Also:Oracle Database Utilities, for more information about Oracle Data Pump and its command-line clients,
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:
x4a has created a table
x4a has a registered XML schema,
ipo, which created two ordered collection tables
x4a creates table
po2 as shown in Example 36-3.
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;
po2 is then populated and exported, as shown in Example 36-4.
Example 36-4 exports all of the following:
All data types that were generated during registration of XML schema
po2 and the ordered collection tables
sitem_nt2, which were generated during registration of XML schema
All data in all of those tables.
Example 36-5 imports all of the data in
x4a.dmp to another database, in which the user
x4a already exists.
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.