1.9 Exporting and Importing Topology Data

You can export a topology from one database and import it into a new topology with the same name, structures, and data in another database, as long as the target database does not already contain a topology with the same name as the exported topology.

To export topology data from one database and import it into another database, follow the steps in this section.

Note:

The steps are required regardless of whether the topology data is transported using transportable tablespaces. (For detailed information about transportable tablespaces and transporting tablespaces to other databases, see Oracle Database Administrator's Guide.)

In the database with the topology data to be exported, perform the following actions:

1. Connect to the database as the owner of the topology.

2. Execute the SDO_TOPO.PREPARE_FOR_EXPORT procedure (documented in SDO_TOPO Package Subprograms), to create the topology export information table, with a name in the format <topology-name>_EXP$. (This table contains the same columns as the USER_SDO_TOPO_INFO and ALL_SDO_TOPO_INFO views. These columns are described in Table 1-8 in xxx_SDO_TOPO_INFO Views.)

   For example, preparing the sample CITY_DATA topology for export creates the CITY_DATA_EXP$ table.

3. Export all tables related to the topology, including the feature tables and the <topology-name>_EDGE$, <topology-name>_FACE$, <topology-name>_HISTORY$, <topology-name>_NODE$, <topology-name>_RELATION$, and <topology-name>_EXP$ tables. The names of feature tables (if they exist) are stored in the topology metadata.

   This creates a file with the extension .dmp (for example, city_data.dmp).

In the database into which to import the topology data, perform the following actions:

1. Connect to the target database, that is, the database in which to create a topology with the same name, structures, and data as the topology exported from the source database. Connect as the user for the schema that is to own the topology to be created.

2. Ensure that the target database does not already contain a topology with the same name as the topology in the .dmp file.

3. Import the tables from the .dmp file that you created when you exported the topology data. Specify the indexes=N option.

4. If you have imported the topology tables into a different schema than the one used for the topology in the source database, update the values in the OWNER and TABLE_SCHEMA columns in all rows of the <topology-name>_EXP$ table to reflect the table owner and schema names in the current (target) database.

5. Execute the SDO_TOPO.INITIALIZE_AFTER_IMPORT procedure, which creates the topology and performs other operations, as necessary, to make the topology ready for use.