15 Transporting Data

Purpose of Transporting Data

You can transport data at any of the following levels:

• Database

  You can use the full transportable export/import feature to move an entire database to a different database instance.

• Tablespaces

  You can use the transportable tablespaces feature to move a set of tablespaces between databases.

• Tables, partitions, and subpartitions

  You can use the transportable tables feature to move a set of tables, partitions, and subpartitions between databases.

Transporting data is much faster than performing either an export/import or unload/load of the same data. It is faster because, for user-defined tablespaces, the data files containing all of the actual data are copied to the target location, and you use Data Pump to transfer only the metadata of the database objects to the new database.

Transportable tablespaces and transportable tables only transports data that resides in user-defined tablespaces. However, full transportable export/import transports data that resides in both user-defined and administrative tablespaces, such as SYSTEM and SYSAUX. Full transportable export/import transports metadata for objects contained within the user-defined tablespaces and both the metadata and data for user-defined objects contained within the administrative tablespaces. Specifically, with full transportable export/import, the export dump file includes only the metadata for objects contained within the user-defined tablespaces, but it includes both the metadata and the data for user-defined objects contained within the administrative tablespaces.

Transporting Data: Scenarios

Transporting data is useful in several scenarios.

This section contains the following topics:

• Scenarios for Full Transportable Export/import

• Scenarios for Transportable Tablespaces or Transportable Tables

CREATE TABLE sales (invoice_no NUMBER,
   sale_year  INT NOT NULL,
   sale_month INT NOT NULL,
   sale_day   INT NOT NULL)
   PARTITION BY RANGE (sale_year, sale_month, sale_day)
     (partition jan2011 VALUES LESS THAN (2011, 2, 1),
      partition feb2011 VALUES LESS THAN (2011, 3, 1),
      partition mar2011 VALUES LESS THAN (2011, 4, 1),
      partition apr2011 VALUES LESS THAN (2011, 5, 1),
      partition may2011 VALUES LESS THAN (2011, 6, 1),
      partition jun2011 VALUES LESS THAN (2011, 7, 1));

CREATE INDEX sales_index ON sales(invoice_no) LOCAL;

ALTER TABLE sales ADD PARTITION jul2011 VALUES LESS THAN (2011, 8, 1);

ALTER TABLE sales EXCHANGE PARTITION jul2011 WITH TABLE jul_sales 
   INCLUDING INDEXES
   WITHOUT VALIDATION;

ORA-19728: data object number conflict between table JUL_SALES and partition JAN2011 in table SALES

ALTER TABLE sales MOVE PARTITION jan2011;

impdp user_name/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
   TRANSPORT_DATAFILES='D:\catalog.f'  

Transporting Data Across Platforms

You can transport data across platforms. This capability can be used to:

• Allow a database to be migrated from one platform to another

• Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms

• Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms

• Enable the sharing of read-only tablespaces between Oracle Database installations on different operating systems or platforms, assuming that your storage system is accessible from those platforms and the platforms all have the same endianness, as described in the sections that follow.

Many, but not all, platforms are supported for cross-platform data transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering). The following query displays the platforms that support cross-platform data transport:

COLUMN PLATFORM_NAME FORMAT A40
COLUMN ENDIAN_FORMAT A14
 
SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT 
  FROM V$TRANSPORTABLE_PLATFORM
  ORDER BY PLATFORM_ID;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- --------------
          1 Solaris[tm] OE (32-bit)                  Big
          2 Solaris[tm] OE (64-bit)                  Big
          3 HP-UX (64-bit)                           Big
          4 HP-UX IA (64-bit)                        Big
          5 HP Tru64 UNIX                            Little
          6 AIX-Based Systems (64-bit)               Big
          7 Microsoft Windows IA (32-bit)            Little
          8 Microsoft Windows IA (64-bit)            Little
          9 IBM zSeries Based Linux                  Big
         10 Linux IA (32-bit)                        Little
         11 Linux IA (64-bit)                        Little
         12 Microsoft Windows x86 64-bit             Little
         13 Linux x86 64-bit                         Little
         15 HP Open VMS                              Little
         16 Apple Mac OS                             Big
         17 Solaris Operating System (x86)           Little
         18 IBM Power Based Linux                    Big
         19 HP IA Open VMS                           Little
         20 Solaris Operating System (x86-64)        Little
         21 Apple Mac OS (x86-64)                    Little

If source platform and the target platform are of the same endianness, then no conversion is necessary, and data can be transported as if they were on the same platform.

If the source platform and the target platform are of different endianness, then the data being transported must be converted to the target platform format. You can convert the data using one of the following methods:

• The GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package

  When you use one of these procedures to move data files between the source platform and the target platform, each block in each data file is converted to the target platform's endianness. The conversion occurs on the target platform.

• The RMAN CONVERT command

  Running the RMAN CONVERT command is an additional step that can be completed on the source or target platform. It converts the data being transported to the target platform format.

Before the data in a data file can be transported to a different platform, the data file header must identify the platform to which it belongs. When you are transporting read-only tablespaces between Oracle Database installations on different platforms, you can accomplish this by making the data file read/write at least once.

General Limitations on Transporting Data

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

• The source and the target databases must use compatible database character sets. Specifically, one of the following must be true:

  • The database character sets of the source and the target databases are the same.

  • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:

    • The source database is Oracle Database 10g Release 1 (10.1.0.3) or later.

    • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.

    • The data to be transported contains no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.

  • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:

    • The source database is before Oracle Database 10g Release 1 (10.1.0.3).

    • The maximum character width is the same in the source and target database character sets.

  Note:

  The subset-superset relationship between character sets recognized by Oracle Database is documented in the Oracle Database Globalization Support Guide.

• The source and the target databases must use compatible national character sets. Specifically, one of the following must be true:

  • The national character sets of the source and target databases are the same.

  • The source database is Oracle Database 10g Release 1 (10.1.0.3) or later and the tablespaces to be transported contain no columns with NCHAR, NVARCHAR2, or NCLOB data type.

• When running a transportable export operation, the following limitations apply:

  • The default tablespace of the user running performing the export must not be one of the tablespaces being transported.

  • The default tablespace of the user running performing the export must be writable.

• In a non-CDB, you cannot transport a tablespace to a target database that contains a tablespace of the same name.

  In a CDB, you cannot transport a tablespace to a target container that contains a tablespace of the same name. However, different containers can have tablespaces with the same name.

  You can use the REMAP_TABLESPACE import parameter to import the database objects into a different tablespace. Alternatively, before the transport operation, you can rename either the tablespace to be transported or the target tablespace.

• In a CDB, the default Data Pump directory object, DATA_PUMP_DIR, does not work with PDBs. You must define an explicit directory object within the PDB that you are using with Data Pump export/import.

• Transporting data 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.

  • 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, a message is displayed during import.

  • You must use only Data Pump to export and import the metadata for data that contains XMLTypes.

  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.

• Types whose interpretation is application-specific and opaque to the database (such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation.

• When you transport a tablespace containing tables with TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) data between databases with different time zones, the tables with the TSLTZ data are not transported. Error messages describe the tables that were not transported. However, tables in the tablespace that do not contain TSLTZ data are transported.

  You can determine the time zone of a database with the following query:

  SELECT DBTIMEZONE FROM DUAL;
  

  You can alter the time zone for a database with an ALTER DATABASE SQL statement.

  You can use Data Pump to perform a conventional export/import of tables with TSLTZ data after the transport operation completes.

• Analytic workspaces cannot be part of cross-platform transport operations. If the source platform and target platform are different, then use Data Pump export/import to export and import analytic workspaces. See Oracle OLAP DML Reference for more information about analytic workspaces.

Certain limitations are specific to full transportable export/import, transportable tablespaces, or transportable tables. See the appropriate section for information:

• "Limitations on Full Transportable Export/import"

• "Limitations on Transportable Tablespaces"

• "Limitations on Transportable Tables"

Compatibility Considerations for Transporting Data

When transporting data, Oracle Database computes the lowest compatibility level at which the target database must run. A tablespace or table can always be transported to a database with the same or higher compatibility setting using transportable tablespaces, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the source database is higher than the compatibility level of the target database.

The following table shows the minimum compatibility requirements of the source and target databases in various scenarios. The source and target database need not have the same compatibility setting.

When you use full transportable export/import, the source database must be an Oracle Database 11g Release 2 (11.2.0.3) or later database, and the target database must be an Oracle Database 12c database. When transporting a database from Oracle Database 11g Release 2 (11.2.0.3) or a later to Oracle Database 12c, the VERSION Data Pump export parameter must be set to 12 or higher. When transporting a database from Oracle Database 12c to Oracle Database 12c, the COMPATIBLE initialization parameter must be set to 12.0.0 or higher.

Introduction to Full Transportable Export/Import

You can use the full transportable export/import feature to copy an entire database from one Oracle Database instance to another. You can use Data Pump to produce an export dump file, transport the dump file to the target database if necessary, and then import the export dump file. Alternatively, you can use Data Pump to copy the database over the network.

The tablespaces in the database being transported can be either dictionary managed or locally managed. The tablespaces in the database are not required to be of the same block size as the target database standard block size.

Limitations on Full Transportable Export/import

Be aware of the following limitations on full transportable export/import:

• The general limitations described in "General Limitations on Transporting Data" apply to full transportable export/import.

• You cannot transport an encrypted tablespace to a platform with different endianness.

  To transport an encrypted tablespace to a platform with the same endianness, during export set the ENCRYPTION_PWD_PROMPT export utility parameter to YES, or use the ENCRYPTION_PASSWORD export utility parameter. During import, use the equivalent import utility parameter, and set the value to the same password that was used for the export.

• When transporting a database over the network, tables with LONG or LONG RAW columns that reside in administrative tablespaces (such as SYSTEM or SYSAUX) are not supported.

• Full transportable export/import can export and import user-defined database objects in administrative tablespaces using conventional Data Pump export/import, such as direct path or external table. Administrative tablespaces are non-user tablespaces supplied with Oracle Database, such as the SYSTEM and SYSAUX tablespaces.

• Full transportable export/import cannot transport a database object that is defined in both an administrative tablespace (such as SYSTEM and SYSAUX) and a user-defined tablespace. For example, a partitioned table might be stored in both a user-defined tablespace and an administrative tablespace. If you have such database objects in your database, then you can redefine them before transporting them so that they are stored entirely in either an administrative tablespace or a user-defined tablespace. If the database objects cannot be redefined, then you can use conventional Data Pump export/import.

• When transporting a database over the network using full transportable export/import, auditing cannot be enabled for tables stored in an administrative tablespace (such as SYSTEM and SYSAUX) when the audit trail information itself is stored in a user-defined tablespace. See Oracle Database Security Guide for more information about auditing.

Transporting a Database Using an Export Dump File

The following list of tasks summarizes the process of transporting a database using an export dump file. Details for each task are provided in the subsequent example.

1. At the source database, place each of the user-defined tablespaces in read-only mode and export the database.

   Ensure that the following parameters are set to the specified values:

   • TRANSPORTABLE=ALWAYS

   • FULL=Y

   If the source database is an Oracle Database 11g Release 2 (11.2.0.3) or later Oracle Database 11g database, then you must set the VERSION parameter to 12 or higher.

   If the source database contains any encrypted tablespaces or tablespaces containing tables with encrypted columns, then you must either specify ENCRYPTION_PWD_PROMPT=YES, or specify the ENCRYPTION_PASSWORD parameter.

   The export dump file includes the metadata for objects contained within the user-defined tablespaces and both the metadata and data for user-defined objects contained within the administrative tablespaces, such as SYSTEM and SYSAUX.

2. Transport the export dump file.

   Copy the export dump file to a place that is accessible to the target database.

3. Transport the data files for all of the user-defined tablespaces in the database.

   Copy the data files to a place that is accessible to the target database.

   If the source platform and target platform are different, then check the endian format of each platform by running the query on the V$TRANSPORTABLE_PLATFORM view in "Transporting Data Across Platforms".

   If the source platform's endian format is different from the target platform's endian format, then use one of the following methods to convert the data files:

   • Use the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data files. These procedures convert the data files to the target platform's endian format automatically.

   • Use the RMAN CONVERT command to convert the data files to the target platform's endian format.

   See "Converting Data Between Platforms" for more information.

4. (Optional) Restore the user-defined tablespaces to read/write mode on the source database.

5. At the target database, import the database.

   When the import is complete, the user-defined tablespaces are in read/write mode.

Example

These tasks for transporting a database are illustrated more fully in the example that follows, where it is assumed the following data files and tablespaces exist:

This example makes the following additional assumptions:

• The target database is a new database that is being populated with the data from the source database. The name of the source database is mydb.

• Both the source database and the target database are Oracle Database 12c databases.

Complete the following tasks to transport the database using an export dump file:

Task 1   Generate the Export Dump File

Generate the export dump file by completing the following steps:

1. Start SQL*Plus and connect to the database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

   See "Connecting to the Database with SQL*Plus" for instructions.

2. Make all of the user-defined tablespaces in the database read-only.

   ALTER TABLESPACE sales READ ONLY;
   
   ALTER TABLESPACE customers READ ONLY;
   
   ALTER TABLESPACE employees READ ONLY;
   

3. Invoke the Data Pump export utility as a user with DATAPUMP_EXP_FULL_DATABASE role and specify the full transportable export/import options.

   SQL> HOST
   
   $ expdp user_name full=y dumpfile=expdat.dmp directory=data_pump_dir 
           transportable=always logfile=export.log
   
   Password: password
   

   You must always specify TRANSPORTABLE=ALWAYS, which determines whether the transportable option is used.

   This example specifies the following Data Pump parameters:

   • The FULL parameter specifies that the entire database is being exported.

   • The DUMPFILE parameter specifies the name of the structural information export dump file to be created, expdat.dmp.

   • The DIRECTORY parameter specifies the directory object that points to the operating system or Oracle Automatic Storage Management location of the dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to the user running the Export utility. See Oracle Database SQL Language Reference for information on the CREATE DIRECTORY command.

     In a non-CDB, the directory object DATA_PUMP_DIR is created automatically. Read and write access to this directory is automatically granted to the DBA role, and thus to users SYS and SYSTEM.

     However, the directory object DATA_PUMP_DIR is not created automatically in a PDB. Therefore, when importing into a PDB, create a directory object in the PDB and specify the directory object when you run Data Pump.

     See Also:

     • Oracle Database Utilities for information about the default directory when the DIRECTORY parameter is omitted

     • Chapter 36, "Overview of Managing a Multitenant Environment" for more information about PDBs

   • The LOGFILE parameter specifies the file name of the log file to be written by the export utility. In this example, the log file is written to the same directory as the dump file, but it can be written to a different location.

   To perform a full transportable export on an Oracle Database 11g Release 2 (11.2.0.3) or later Oracle Database 11g database, use the VERSION parameter, as shown in the following example:

   expdp user_name full=y dumpfile=expdat.dmp directory=data_pump_dir 
         transportable=always version=12 logfile=export.log
   

   Full transportable import is supported only for Oracle Database 12c databases.

   Notes:

   In this example, the Data Pump utility is used to export only data dictionary structural information (metadata) for the user-defined tablespaces. Actual data is unloaded only for the administrative tablespaces (SYSTEM and SYSAUX), so this operation goes relatively quickly even for large user-defined tablespaces.

4. Check the log file for errors, and take note of the dump file and data files that you must transport to the target database. expdp outputs the names and paths of these files in messages like these:

   ******************************************************************************
   Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
     /u01/app/oracle/admin/mydb/dpdump/expdat.dmp
   ******************************************************************************
   Datafiles required for transportable tablespace SALES:
     /u01/app/oracle/oradata/mydb/sales01.dbf
   Datafiles required for transportable tablespace CUSTOMERS:
     /u01/app/oracle/oradata/mydb/cust01.dbf
   Datafiles required for transportable tablespace EMPLOYEES:
     /u01/app/oracle/oradata/mydb/emp01.dbf
   

5. When finished, exit back to SQL*Plus:

   $ exit
   

See Also:

Oracle Database Utilities for information about using the Data Pump utility

Task 2   Transport the Export Dump File

Transport the dump file to the directory pointed to by the DATA_PUMP_DIR directory object, or to any other directory of your choosing. The new location must be accessible to the target database.

At the target database, run the following query to determine the location of DATA_PUMP_DIR:

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

OWNER      DIRECTORY_NAME   DIRECTORY_PATH
---------- ---------------- -----------------------------------
SYS        DATA_PUMP_DIR    C:\app\orauser\admin\orawin\dpdump\

Task 3   Transport the Data Files for the User-Defined Tablespaces

Transport the data files of the user-defined tablespaces in the database to a place that is accessible to the target database.

In this example, transfer the following data files from the source database to the target database:

• sales01.dbf

• cust01.dbf

• emp01.dbf

If you are transporting the database to a platform different from the source platform, then determine if cross-platform database transport is supported for both the source and target platforms, and determine the endianness of each platform. If both platforms have the same endianness, then no conversion is necessary. Otherwise you must do a conversion of each tablespace in the database either at the source or target database.

If you are transporting the database to a different platform, you can execute the following query on each platform. If the query returns a row, then the platform supports cross-platform tablespace transport.

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

The following is the query result from the source platform:

PLATFORM_NAME                      ENDIAN_FORMAT
---------------------------------- --------------
Solaris[tm] OE (32-bit)            Big

The following is the query result from the target platform:

PLATFORM_NAME                      ENDIAN_FORMAT
---------------------------------- --------------
Microsoft Windows IA (32-bit)      Little

In this example, you can see that the endian formats are different. Therefore, in this case, a conversion is necessary for transporting the database. Use either the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data files. These procedures convert the data files to the target platform's endian format automatically. Transport the data files to the location of the existing data files of the target database. On the UNIX and Linux platforms, this location is typically /u01/app/oracle/oradata/dbname/ or +DISKGROUP/dbname/datafile/. Alternatively, you can use the RMAN CONVERT command to convert the data files. See "Converting Data Between Platforms" for more information.

Note:

If no endianness conversion of the tablespaces is needed, then you can transfer the files using any file transfer method.

See Also:

"Guidelines for Transferring Data Files"

Task 4   (Optional) Restore Tablespaces to Read/Write Mode

Make the transported tablespaces read/write again at the source database, as follows:

ALTER TABLESPACE sales READ WRITE;
ALTER TABLESPACE customers READ WRITE;
ALTER TABLESPACE employees READ WRITE;

You can postpone this task to first ensure that the import process succeeds.

Task 5   At the Target Database, Import the Database

Invoke the Data Pump import utility as a user with DATAPUMP_IMP_FULL_DATABASE role and specify the full transportable export/import options.

impdp user_name full=Y dumpfile=expdat.dmp directory=data_pump_dir
   transport_datafiles=
      '/u01/app/oracle/oradata/mydb/sales01.dbf',
      '/u01/app/oracle/oradata/mydb/cust01.dbf',
      '/u01/app/oracle/oradata/mydb/emp01.dbf'
   logfile=import.log

Password: password

This example specifies the following Data Pump parameters:

• The FULL parameter specifies that the entire database is being imported in FULL mode.

• The DUMPFILE parameter specifies the exported file containing the metadata for the user-defined tablespaces and both the metadata and data for the administrative tablespaces to be imported.

• The DIRECTORY parameter specifies the directory object that identifies the location of the export dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to the user running the Import utility. See Oracle Database SQL Language Reference for information on the CREATE DIRECTORY command.

  In a non-CDB, the directory object DATA_PUMP_DIR is created automatically. Read and write access to this directory is automatically granted to the DBA role, and thus to users SYS and SYSTEM.

  However, the directory object DATA_PUMP_DIR is not created automatically in a PDB. Therefore, when importing into a PDB, create a directory object in the PDB and specify the directory object when you run Data Pump.

  See Also:

  • Oracle Database Utilities for information about the default directory when the DIRECTORY parameter is omitted

  • Chapter 36, "Overview of Managing a Multitenant Environment" for more information about PDBs

• The TRANSPORT_DATAFILES parameter identifies all of the data files to be imported.

  You can specify the TRANSPORT_DATAFILES parameter multiple times in a parameter file specified with the PARFILE parameter if there are many data files.

• The LOGFILE parameter specifies the file name of the log file to be written by the import utility. In this example, the log file is written to the directory from which the dump file is read, but it can be written to a different location.

After this statement executes successfully, check the import log file to ensure that no unexpected error has occurred.

When dealing with a large number of data files, specifying the list of data file names in the statement line can be a laborious process. It can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:

impdp user_name parfile='par.f'

For example, par.f might contain the following lines:

FULL=Y
DUMPFILE=expdat.dmp 
DIRECTORY=data_pump_dir
TRANSPORT_DATAFILES=
'/u01/app/oracle/oradata/mydb/sales01.dbf',
'/u01/app/oracle/oradata/mydb/cust01.dbf',
'/u01/app/oracle/oradata/mydb/emp01.dbf'
LOGFILE=import.log

Note:

• During the import, user-defined tablespaces might be temporarily made read/write for metadata loading. Ensure that no user changes are made to the data during the import. At the successful completion of the import, all user-defined tablespaces are made read/write.

• When performing a network database import, the TRANSPORTABLE parameter must be set to always.

• When you are importing into a PDB in a CDB, specify the connect identifier for the PDB after the user name. For example, if the connect identifier for the PDB is hrpdb, then enter the following when you run the Oracle Data Pump Import utility:

  impdp user_name@hrpdb ...
  

See Also:

• Oracle Database Utilities for information about using the import utility

• Part VI, "Managing a Multitenant Environment"

Transporting a Database Over the Network

To transport a database over the network, you perform an import using the NETWORK_LINK parameter, the import is performed using a database link, and there is no dump file involved.

The following list of tasks summarizes the process of transporting a database over the network. Details for each task are provided in the subsequent example.

1. Create a database link from the target database to the source database.

   The import operation must be performed by a user on the target database with DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user on the source database with DATAPUMP_EXP_FULL_DATABASE role. The user on the source database cannot be a user with SYSDBA administrative privilege. If the database link is a connected user database link, then the user on the target database cannot be a user with SYSDBA administrative privilege. See "Users of Database Links" for information about connected user database links.

2. In the source database, make the user-defined tablespaces in the database read-only.

3. Transport the data files for the all of the user-defined tablespaces in the database.

   Copy the data files to a place that is accessible to the target database.

   If the source platform and target platform are different, then check the endian format of each platform by running the query on the V$TRANSPORTABLE_PLATFORM view in "Transporting Data Across Platforms".

   If the source platform's endian format is different from the target platform's endian format, then use one of the following methods to convert the data files:

   • Use the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data files. These procedures convert the data files to the target platform's endian format automatically.

   • Use the RMAN CONVERT command to convert the data files to the target platform's endian format.

   See "Converting Data Between Platforms" for more information.

4. At the target database, import the database.

   Invoke the Data Pump utility to import the metadata for the user-defined tablespaces and both the metadata and data for the administrative tablespaces.

   Ensure that the following parameters are set to the specified values:

   • TRANSPORTABLE=ALWAYS

   • TRANSPORT_DATAFILES=list_of_datafiles

   • FULL=Y

   • NETWORK_LINK=source_database_link

     Replace source_database_link with the name of the database link to the source database.

   • VERSION=12

     If the source database is an Oracle Database 11g Release 2 (11.2.0.3) or later Oracle Database 11g database, then the VERSION parameter is required and must be set to 12. If the source database is an Oracle Database 12c database, then the VERSION parameter is not required.

   If the source database contains any encrypted tablespaces or tablespaces containing tables with encrypted columns, then you must either specify ENCRYPTION_PWD_PROMPT=YES, or specify the ENCRYPTION_PASSWORD parameter.

   The Data Pump network import copies the metadata for objects contained within the user-defined tablespaces and both the metadata and data for user-defined objects contained within the administrative tablespaces, such as SYSTEM and SYSAUX.

   When the import is complete, the user-defined tablespaces are in read/write mode.

5. (Optional) Restore the user-defined tablespaces to read/write mode on the source database.

Example

These tasks for transporting a database are illustrated more fully in the example that follows, where it is assumed the following data files and tablespaces exist:

This example makes the following additional assumptions:

• The target database is a new database that is being populated with the data from the source database. The name of the source database is sourcedb.

• The source database and target database are running on the same platform with the same endianness.

  To check the endianness of a platform, run the following query:

  SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
       FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
  

• The sales tablespace is encrypted. The other tablespaces are not encrypted.

• The source database is an Oracle Database 11g Release 2 (11.2.0.3) database and the target database is an Oracle Database 12c database.

Complete the following tasks to transport the database over the network:

Task 1   Create a Database Link from the Target Database to the Source Database

Create a database link from the target database to the source database by completing the following steps:

1. Ensure that network connectivity is configured between the source database and the target database.

   See Oracle Database Net Services Administrator's Guide for instructions.

2. Start SQL*Plus and connect to the target database as the administrator who will transport the database with Data Pump import. This user must have DATAPUMP_IMP_FULL_DATABASE role to transport the database.

   See "Connecting to the Database with SQL*Plus" for instructions.

3. Create the database link:

   CREATE PUBLIC DATABASE LINK sourcedb USING 'sourcedb';
   

   Specify the service name for the source database in the using clause.

   During the import operation, the database link must connect to a user on the source database with DATAPUMP_EXP_FULL_DATABASE role. The user on the source database cannot be a user with SYSDBA administrative privilege.

See Also:

• "Creating Database Links"

• Oracle Database SQL Language Reference

Task 2   Make the User-Defined Tablespaces Read-Only

Complete the following steps:

1. Start SQL*Plus and connect to the source database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

   See "Connecting to the Database with SQL*Plus" for instructions.

2. Make all of the user-defined tablespaces in the database read-only.

   ALTER TABLESPACE sales READ ONLY;
   
   ALTER TABLESPACE customers READ ONLY;
   
   ALTER TABLESPACE employees READ ONLY;
   

Task 3   Transport the Data Files for the User-Defined Tablespaces

Transport the data files to the location of the existing data files of the target database.

On the UNIX and Linux platforms, this location is typically /u01/app/oracle/oradata/dbname/ or +DISKGROUP/dbname/datafile/.

In this example, transfer the following data files from the source database to the target database:

• sales01.dbf

• cust01.dbf

• emp01.dbf

See Also:

"Guidelines for Transferring Data Files"

Task 4   At the Target Database, Import the Database

Invoke the Data Pump import utility as a user with DATAPUMP_IMP_FULL_DATABASE role and specify the full transportable export/import options.

impdp user_name full=Y network_link=sourcedb transportable=always 
   transport_datafiles=
      '/u01/app/oracle/oradata/mydb/sales01.dbf',
      '/u01/app/oracle/oradata/mydb/cust01.dbf',
      '/u01/app/oracle/oradata/mydb/emp01.dbf'
   encryption_pwd_prompt=YES version=12 logfile=import.log

Password: password

This example specifies the following Data Pump parameters:

• The FULL parameter specifies that the entire database is being imported in FULL mode.

• The NETWORK_LINK parameter specifies the database link used for the network import.

• The TRANSPORTABLE parameter specifies that the import uses the transportable option.

• The TRANSPORT_DATAFILES parameter identifies all of the data files to be imported.

  You can specify the TRANSPORT_DATAFILES parameter multiple times in a parameter file specified with the PARFILE parameter if there are many data files.

• The ENCRYPTION_PWD_PROMPT parameter instructs Data Pump to prompt you for the encryption password, and Data Pump encrypts data and metadata sent over the network connection. Either the ENCRYPTION_PWD_PROMPT parameter or the ENCRYPTION_PASSWORD parameter is required when encrypted tablespaces or tables with encrypted columns are part of the import operation.

• The VERSION parameter is set to 12 because the source database is an Oracle Database 11g Release 2 (11.2.0.3) or later Oracle Database 11g database.

• The LOGFILE parameter specifies the file name of the log file to be written by the import utility.

After this statement executes successfully, check the import log file to ensure that no unexpected error has occurred.

When dealing with a large number of data files, specifying the list of data file names in the statement line can be a laborious process. It can even exceed the statement line limit. In this situation, you can use an import parameter file.

Use of an import parameter file is also recommended when encrypted tablespaces or tables with encrypted columns are part of the import operation. In this case, specify ENCRYPTION_PWD_PROMPT=YES in the import parameter file.

For example, you can invoke the Data Pump import utility as follows:

impdp user_name parfile='par.f'

For example, par.f might contain the following lines:

FULL=Y
NETWORK_LINK=sourcedb 
TRANSPORTABLE=always
TRANSPORT_DATAFILES=
'/u01/app/oracle/oradata/mydb/sales01.dbf',
'/u01/app/oracle/oradata/mydb/cust01.dbf',
'/u01/app/oracle/oradata/mydb/emp01.dbf'
ENCRYPTION_PWD_PROMPT=YES
VERSION=12
LOGFILE=import.log

Note:

• During the import, user-defined tablespaces might be temporarily made read/write for metadata loading. Ensure that no user changes are made to the data during the import. At the successful completion of the import, all user-defined tablespaces are made read/write.

• When you are importing into a PDB in a CDB, specify the connect identifier for the PDB after the user name. For example, if the connect identifier for the PDB is hrpdb, then enter the following when you run the Oracle Data Pump Import utility:

  impdp user_name@hrpdb ...
  

See Also:

Oracle Database Utilities for information about using the import utility

Task 5   (Optional) Restore User-Defined Tablespaces to Read/Write Mode

Make the user-defined tablespaces read/write again at the source database, as follows:

ALTER TABLESPACE sales READ WRITE;

ALTER TABLESPACE customers READ WRITE;

ALTER TABLESPACE employees READ WRITE;

You can postpone this task to first ensure that the import process succeeds.

Introduction to Transportable Tablespaces

You can use the transportable tablespaces feature to copy a set of tablespaces from one Oracle Database to another.

The tablespaces being transported can be either dictionary managed or locally managed. The transported tablespaces are not required to be of the same block size as the target database standard block size. These scenarios are discussed in "Transporting Data: Scenarios".

There are two ways to transport a tablespace:

• Manually, following the steps described in this section. This involves issuing commands to SQL*Plus and Data Pump.

• Using the Transport Tablespaces Wizard in Oracle Enterprise Manager Cloud Control

  To run the Transport Tablespaces Wizard:

  1. Log in to Cloud Control with a user that has the DATAPUMP_EXP_FULL_DATABASE role.

  2. Access the Database Home page.

  3. From the Schema menu, select Database Export/Import, then Transport Tablespaces.

Limitations on Transportable Tablespaces

Be aware of the following limitations for transportable tablespaces:

• The general limitations described in "General Limitations on Transporting Data" apply to transportable tablespaces.

• When transporting a tablespace set, 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.

• Transportable tablespaces cannot transport tables with TIMESTAMP WITH TIMEZONE (TSTZ) data across platforms with different time zone file versions. These tables are skipped automatically in a transportable tablespaces operation. These tables can be exported and imported conventionally.

  See Oracle Database Utilities for more information.

• The following are limitations related to encryption:

  • Transportable tablespaces cannot transport encrypted tablespaces.

  • Transportable tablespaces cannot transport tablespaces containing tables with encrypted columns.

• Administrative tablespaces, such as SYSTEM and SYSAUX, cannot be included in a transportable tablespace set.

Transporting Tablespaces Between Databases

The following list of tasks summarizes the process of transporting a tablespace. Details for each task are provided in the subsequent example.

1. Pick a self-contained set of tablespaces.

2. At the source database, place the set of tablespaces in read-only mode and generate a transportable tablespace set.

   A transportable tablespace set (or transportable set) consists of data files for the set of tablespaces being transported and an export dump file containing structural information (metadata) for the set of tablespaces. You use Data Pump to perform the export.

3. Transport the export dump file.

   Copy the export dump file to a place that is accessible to the target database.

4. Transport the tablespace set.

   Copy the data files to a place that is accessible to the target database.

   If the source platform and target platform are different, then check the endian format of each platform by running the query on the V$TRANSPORTABLE_PLATFORM view in "Transporting Data Across Platforms".

   If the source platform's endian format is different from the target platform's endian format, then use one of the following methods to convert the data files:

   • Use the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data files. These procedures convert the data files to the target platform's endian format automatically.

   • Use the RMAN CONVERT command to convert the data files to the target platform's endian format.

   See "Converting Data Between Platforms" for more information.

5. (Optional) Restore tablespaces to read/write mode on the source database.

6. At the target database, import the tablespace set.

   Invoke the Data Pump utility to import the metadata for the tablespace set.

Example

These tasks for transporting a tablespace are illustrated more fully in the example that follows, where it is assumed the following data files and tablespaces exist:

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
---------------------------------------------------------------------------
Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table
JIM.DEPT in tablespace OTHER
Partitioned table JIM.SALES is partially contained in the transportable set

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

OWNER      DIRECTORY_NAME   DIRECTORY_PATH
---------- ---------------- -----------------------------------
SYS        DATA_PUMP_DIR    C:\app\orauser\admin\orawin\dpdump\

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME                      ENDIAN_FORMAT
---------------------------------- --------------
Solaris[tm] OE (32-bit)            Big

PLATFORM_NAME                      ENDIAN_FORMAT
---------------------------------- --------------
Microsoft Windows IA (32-bit)      Little

ALTER TABLESPACE sales_1 READ WRITE;
ALTER TABLESPACE sales_2 READ WRITE;

Introduction to Transportable Tables

You can use the transportable tables feature to copy a set of tables, partitions, or subpartitions from one Oracle Database to another. A transportable tables operation moves metadata for the specified tables, partitions, or subpartitions to the target database.

A transportable tables operation automatically identifies the tablespaces used by the specified tables. To move the data, you copy the data files for these tablespaces to the target database. The Data Pump import automatically frees the blocks in the data files occupied by tables, partitions, or subpartitions that were not part of the transportable tables operation. It also frees the blocks occupied by the dependent objects of the tables that were not part of the transportable tables operation.

You can transport the tables, partitions, and subpartitions in the following ways:

• Using an export dump file

  During the export, specify the TABLES parameter and set the TRANSPORTABLE parameter to ALWAYS. During import, do not specify the TRANSPORTABLE parameter. Data Pump import recognizes the transportable tables operation automatically.

• Over the network

  During the import, specify the TABLES parameter, set the TRANSPORTABLE parameter to ALWAYS, and specify the NETWORK_LINK parameter to identify the source database.

Limitations on Transportable Tables

Be aware of the following limitations for transportable tables:

• The general limitations described in "General Limitations on Transporting Data" apply to transportable tables.

• You cannot transport a table to a target database that contains a table of the same name in the same schema. However, you can use the REMAP_TABLE import parameter to import the data into a different table. Alternatively, before the transport operation, you can rename either the table to be transported or the target table.

• The following are limitations related to encryption:

  • You cannot transport tables that are in encrypted tablespaces.

  • You cannot transport tables with encrypted columns.

• You cannot transport tables with TIMESTAMP WITH TIMEZONE (TSTZ) data across platforms with different time zone file versions.

  See Oracle Database Utilities for more information.

Transporting Tables, Partitions, or Subpartitions Using an Export Dump File

The following list of tasks summarizes the process of transporting tables between databases using an export dump file. Details for each task are provided in the subsequent example.

1. Pick a set of tables, partitions, or subpartitions.

   If you are transporting partitions, then you can specify partitions from only one table in a transportable tables operation, and no other tables can be transported in the same operation. Also, if only a subset of a table's partitions are exported in a transportable tables operation, then on import each partition becomes a non-partitioned table.

2. At the source database, place the tablespaces associated with the data files for the tables, partitions, or subpartitions in read-only mode.

   To view the tablespace for a table, query the DBA_TABLES view. To view the data file for a tablespace, query the DBA_DATA_FILES view.

3. Perform the Data Pump export.

4. Transport the export dump file.

   Copy the export dump file to a place that is accessible to the target database.

5. Transport the data files for the tables, partitions, or subpartitions.

   Copy the data files to a place that is accessible to the target database.

   If the source platform and target platform are different, then check the endian format of each platform by running the query on the V$TRANSPORTABLE_PLATFORM view in "Transporting Data Across Platforms".

   If the source platform's endian format is different from the target platform's endian format, then use one of the following methods to convert the data files:

   • Use the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data files. These procedures convert the data files to the target platform's endian format automatically.

   • Use the RMAN CONVERT command to convert the data files to the target platform's endian format.

   See "Converting Data Between Platforms" for more information.

6. (Optional) Restore tablespaces to read/write mode on the source database.

7. At the target database, perform the import.

   Invoke the Data Pump utility to import the metadata for the tables.

Example

These tasks for transporting tables, partitions, and subpartitions using a Data Pump dump file are illustrated more fully in the example that follows, where it is assumed that the following partitions exist in the sh.sales_prt table:

• sales_q1_2000

• sales_q2_2000

• sales_q3_2000

• sales_q4_2000

This example transports two of these partitions to the target database.

The following SQL statements create the sales_prt table and its and partitions in the sh schema and the tablespace and data file for the table. The statements also insert data into the partitions by using data in the sh sample schemas.

CREATE TABLESPACE sales_prt_tbs
   DATAFILE 'sales_prt.dbf' SIZE 20M 
   ONLINE; 
 
CREATE TABLE sh.sales_prt
    (prod_id        NUMBER(6), 
     cust_id        NUMBER, 
     time_id        DATE, 
     channel_id     CHAR(1), 
     promo_id       NUMBER(6), 
     quantity_sold  NUMBER(3), 
     amount_sold    NUMBER(10,2))
        PARTITION BY RANGE (time_id)
         (PARTITION SALES_Q1_2000 VALUES LESS THAN 
                          (TO_DATE('01-APR-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')),
          PARTITION SALES_Q2_2000 VALUES LESS THAN 
                        (TO_DATE('01-JUL-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')),
          PARTITION SALES_Q3_2000 VALUES LESS THAN 
                          (TO_DATE('01-OCT-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')),
          PARTITION SALES_Q4_2000 VALUES LESS THAN 
                          (TO_DATE('01-JAN-2001','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')))
 TABLESPACE sales_prt_tbs;
 
INSERT INTO sh.sales_prt PARTITION(sales_q1_2000) 
  SELECT * FROM sh.sales PARTITION(sales_q1_2000);

INSERT INTO sh.sales_prt PARTITION(sales_q2_2000) 
  SELECT * FROM sh.sales PARTITION(sales_q2_2000);

INSERT INTO sh.sales_prt PARTITION(sales_q3_2000) 
  SELECT * FROM sh.sales PARTITION(sales_q3_2000);
 
INSERT INTO sh.sales_prt PARTITION(sales_q4_2000) 
  SELECT * FROM sh.sales PARTITION(sales_q4_2000);

COMMIT;

This example makes the following additional assumptions:

• The name of the source database is sourcedb.

• The source database and target database are running on the same platform with the same endianness. To check the endianness of a platform, run the following query:

  SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
       FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
  

• Only the sales_q1_2000 and sales_q2_2000 partitions are transported to the target database. The other two partitions are not transported.

Complete the following tasks to transport the partitions using an export dump file:

Task 1   Generate the Export Dump File

Generate the export dump file by completing the following steps:

1. Start SQL*Plus and connect to the source database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

   See "Connecting to the Database with SQL*Plus" for instructions.

2. Make all of the tablespaces that contain the tables being transported read-only.

   ALTER TABLESPACE sales_prt_tbs READ ONLY;
   

3. Invoke the Data Pump export utility as a user with DATAPUMP_EXP_FULL_DATABASE role and specify the transportable tables options.

   SQL> HOST
   
   expdp user_name dumpfile=sales_prt.dmp  directory=data_pump_dir 
         tables=sh.sales_prt:sales_q1_2000,sh.sales_prt:sales_q2_2000
         transportable=always logfile=exp.log
   
   Password: password
   

   You must always specify TRANSPORTABLE=ALWAYS, which specifies that the transportable option is used.

   This example specifies the following additional Data Pump parameters:

   • The DUMPFILE parameter specifies the name of the structural information export dump file to be created, sales_prt.dmp.

   • The DIRECTORY parameter specifies the directory object that points to the operating system or Oracle Automatic Storage Management location of the dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to the user running the Export utility. See Oracle Database SQL Language Reference for information on the CREATE DIRECTORY command.

     In a non-CDB, the directory object DATA_PUMP_DIR is created automatically. Read and write access to this directory is automatically granted to the DBA role, and thus to users SYS and SYSTEM.

     However, the directory object DATA_PUMP_DIR is not created automatically in a PDB. Therefore, when importing into a PDB, create a directory object in the PDB and specify the directory object when you run Data Pump.

     See Also:

     • Oracle Database Utilities for information about the default directory when the DIRECTORY parameter is omitted

     • Chapter 36, "Overview of Managing a Multitenant Environment" for more information about PDBs

   • The TABLES parameter specifies the tables, partitions, or subpartitions being exported.

   • The LOGFILE parameter specifies the file name of the log file to be written by the export utility. In this example, the log file is written to the same directory as the dump file, but it can be written to a different location.

4. Check the log file for unexpected errors, and take note of the dump file and data files that you must transport to the target database. expdp outputs the names and paths of these files in messages like these:

   Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
   Processing object type TABLE_EXPORT/TABLE/TABLE
   Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
   Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
   ******************************************************************************
   Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
     /u01/app/oracle/rdbms/log/sales_prt.dmp
   ******************************************************************************
   Datafiles required for transportable tablespace SALES_PRT_TBS:
     /u01/app/oracle/oradata/sourcedb/sales_prt.dbf
   Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 11:32:13
   

5. When finished, exit back to SQL*Plus:

   $ exit
   

See Also:

Oracle Database Utilities for information about using the Data Pump utility

Task 2   Transport the Export Dump File

Transport the dump file to the directory pointed to by the DATA_PUMP_DIR directory object on the target database, or to any other directory of your choosing. The new location must be accessible to the target database.

In this example, transfer the sales_prt.dmp dump file from the source database to the target database.

At the target database, run the following query to determine the location of DATA_PUMP_DIR:

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

OWNER      DIRECTORY_NAME   DIRECTORY_PATH
---------- ---------------- -----------------------------------
SYS        DATA_PUMP_DIR    /u01/app/oracle/rdbms/log/

Task 3   Transport the Data Files for the Tables

Transport the data files of the tablespaces containing the tables being transported to a place that is accessible to the target database.

Typically, you transport the data files to the location of the existing data files of the target database. On the UNIX and Linux platforms, this location is typically /u01/app/oracle/oradata/dbname/ or +DISKGROUP/dbname/datafile/.

In this example, transfer the sales_prt.dbf data file from the source database to the target database.

See Also:

"Guidelines for Transferring Data Files"

Task 4   (Optional) Restore Tablespaces to Read/Write Mode

Make the tablespaces that contain the tables being transported read/write again at the source database, as follows:

ALTER TABLESPACE sales_prt_tbs READ WRITE;

You can postpone this task to first ensure that the import process succeeds.

Task 5   At the Target Database, Import the Partitions

At the target database, invoke the Data Pump import utility as a user with DATAPUMP_IMP_FULL_DATABASE role and specify the transportable tables options.

impdp user_name dumpfile=sales_prt.dmp directory=data_pump_dir
   transport_datafiles='/u01/app/oracle/oradata/targetdb/sales_prt.dbf'
   tables=sh.sales_prt:sales_q1_2000,sh.sales_prt:sales_q2_2000
   logfile=imp.log

Password: password

This example specifies the following Data Pump parameters:

• The DUMPFILE parameter specifies the exported file containing the metadata for the data to be imported.

• The DIRECTORY parameter specifies the directory object that identifies the location of the export dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to the user running the Import utility. See Oracle Database SQL Language Reference for information on the CREATE DIRECTORY command.

  In a non-CDB, the directory object DATA_PUMP_DIR is created automatically. Read and write access to this directory is automatically granted to the DBA role, and thus to users SYS and SYSTEM.

  However, the directory object DATA_PUMP_DIR is not created automatically in a PDB. Therefore, when importing into a PDB, create a directory object in the PDB and specify the directory object when you run Data Pump.

  See Also:

  • Oracle Database Utilities for information about the default directory when the DIRECTORY parameter is omitted

  • Chapter 36, "Overview of Managing a Multitenant Environment" for more information about PDBs

• The TRANSPORT_DATAFILES parameter identifies all of the data files to be imported.

  You can specify the TRANSPORT_DATAFILES parameter multiple times in a parameter file specified with the PARFILE parameter if there are many data files.

• The TABLES parameter specifies the tables, partitions, or subpartitions being imported.

• The LOGFILE parameter specifies the file name of the log file to be written by the import utility. In this example, the log file is written to the directory from which the dump file is read, but it can be written to a different location.

After this statement executes successfully, check the import log file to ensure that no unexpected error has occurred.

When dealing with a large number of data files, specifying the list of data file names in the statement line can be a laborious process. It can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:

impdp user_name parfile='par.f'

For example, par.f might contain the following lines:

DUMPFILE=sales_prt.dmp 
DIRECTORY=data_pump_dir
TRANSPORT_DATAFILES='/u01/app/oracle/oradata/targetdb/sales_prt.dbf'
TABLES=sh.sales_prt:sales_q1_2000,sh.sales_prt:sales_q2_2000
LOGFILE=imp.log

Note:

• The partitions are imported as separate tables in the target database because this example transports a subset of partitions.

• During the import, tablespaces might be temporarily made read/write for metadata loading. Ensure that no user changes are made to the data during the import. At the successful completion of the import, all user-defined tablespaces are made read/write.

• When performing a network database import, the TRANSPORTABLE parameter must be set to always.

See Also:

Oracle Database Utilities for information about using the import utility

Transporting Tables, Partitions, or Subpartitions Over the Network

To transport tables over the network, you perform an import using the NETWORK_LINK parameter, the import is performed using a database link, and there is no dump file involved.

The following list of tasks summarizes the process of transporting tables, partitions, and subpartitions between databases over the network. Details for each task are provided in the subsequent example.

1. Pick a set of tables, partitions, or subpartitions.

   If you are transporting partitions, then you can specify partitions from only one table in a transportable tables operation, and no other tables can be transported in the same operation. Also, if only a subset of a table's partitions are exported in a transportable tables operation, then on import each partition becomes a non-partitioned table.

2. At the source database, place the tablespaces associated with the data files for the tables, partitions, or subpartitions in read-only mode.

   To view the tablespace for a table, query the DBA_TABLES view. To view the data file for a tablespace, query the DBA_DATA_FILES view.

3. Transport the data files for the tables, partitions, or subpartitions.

   Copy the data files to a place that is accessible to the target database.

   If the source platform and target platform are different, then check the endian format of each platform by running the query on the V$TRANSPORTABLE_PLATFORM view in "Transporting Data Across Platforms".

   If the source platform's endian format is different from the target platform's endian format, then use one of the following methods to convert the data files:

   • Use the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data files. These procedures convert the data files to the target platform's endian format automatically.

   • Use the RMAN CONVERT command to convert the data files to the target platform's endian format.

   See "Converting Data Between Platforms" for more information.

4. At the target database, perform the import.

   Invoke the Data Pump utility to import the metadata for the tables.

5. (Optional) Restore tablespaces to read/write mode on the source database.

Example

These tasks for transporting tables over the network are illustrated more fully in the example that follows, where it is assumed that the tables exist in the source database:

This example transports these tables to the target database. To complete the example, these tables must exist on the source database.

The following SQL statements create the tables in the hr schema and the tablespaces and data files for the tables. The statements also insert data into the tables by using data in the hr and oe sample schemas.

CREATE TABLESPACE emp_tsp
   DATAFILE 'emp.dbf' SIZE 1M 
   ONLINE;

CREATE TABLE hr.emp_ttbs( 
   employee_id    NUMBER(6), 
   first_name     VARCHAR2(20), 
   last_name      VARCHAR2(25), 
   email          VARCHAR2(25), 
   phone_number   VARCHAR2(20), 
   hire_date      DATE, 
   job_id         VARCHAR2(10), 
   salary         NUMBER(8,2), 
   commission_pct NUMBER(2,2), 
   manager_id     NUMBER(6), 
   department_id  NUMBER(4))
 TABLESPACE emp_tsp;

INSERT INTO hr.emp_ttbs SELECT * FROM hr.employees;

CREATE TABLESPACE orders_tsp
   DATAFILE 'orders.dbf' SIZE 1M 
   ONLINE;

CREATE TABLE oe.orders_ttbs( 
   order_id      NUMBER(12),
   order_date    TIMESTAMP WITH LOCAL TIME ZONE,
   order_mode    VARCHAR2(8),
   customer_id   NUMBER(6),
   order_status  NUMBER(2),
   order_total   NUMBER(8,2),
   sales_rep_id  NUMBER(6),
   promotion_id  NUMBER(6))
 TABLESPACE orders_tsp;

INSERT INTO oe.orders_ttbs SELECT * FROM oe.orders;

COMMIT;

This example makes the following additional assumptions:

• The name of the source database is sourcedb.

• The source database and target database are running on the same platform with the same endianness. To check the endianness of a platform, run the following query:

  SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
       FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
  

Complete the following tasks to transport the tables over the network:

Task 1   Create a Database Link from the Target Database to the Source Database

Create a database link from the target database to the source database by completing the following steps:

1. Ensure that network connectivity is configured between the source database and the target database.

   See Oracle Database Net Services Administrator's Guide for instructions.

2. Start SQL*Plus and connect to the target database as the administrator who will transport the data with Data Pump import. This user must have DATAPUMP_IMP_FULL_DATABASE role to transport the data.

   See "Connecting to the Database with SQL*Plus" for instructions.

3. Create the database link:

   CREATE PUBLIC DATABASE LINK sourcedb USING 'sourcedb';
   

   Specify the service name for the source database in the using clause.

   During the import operation, the database link must connect to a user on the source database with DATAPUMP_EXP_FULL_DATABASE role. The user on the source database cannot be a user with SYSDBA administrative privilege.

See Also:

• "Creating Database Links"

• Oracle Database SQL Language Reference

Task 2   Make the Tablespaces Containing the Tables Read-Only

At the source database, complete the following steps:

1. Start SQL*Plus and connect to the source database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

   See "Connecting to the Database with SQL*Plus" for instructions.

2. Make all of the tablespaces that contain data to be transported read-only.

   ALTER TABLESPACE emp_tsp READ ONLY;
   ALTER TABLESPACE orders_tsp READ ONLY;
   

Task 3   Transport the Data Files for the Tables

Transport the data files of the tablespaces containing the tables being transported to a place that is accessible to the target database.

Typically, you transport the data files to the location of the existing data files of the target database. On the UNIX and Linux platforms, this location is typically /u01/app/oracle/oradata/dbname/ or +DISKGROUP/dbname/datafile/.

In this example, transfer the emp.dbf and orders.dbf data files from the source database to the target database.

See Also:

"Guidelines for Transferring Data Files"

Task 4   At the Target Database, Import the Database

Invoke the Data Pump import utility as a user with DATAPUMP_IMP_FULL_DATABASE role and specify the full transportable export/import options.

impdp user_name network_link=sourcedb transportable=always 
   transport_datafiles=
      '/u01/app/oracle/oradata/targetdb/emp.dbf'
      '/u01/app/oracle/oradata/targetdb/orders.dbf'
   tables=hr.emp_ttbs,oe.orders_ttbs
   logfile=import.log

Password: password

This example specifies the following Data Pump parameters:

• The NETWORK_LINK parameter specifies the database link to the source database used for the network import.

• The TRANSPORTABLE parameter specifies that the import uses the transportable option.

• The TRANSPORT_DATAFILES parameter identifies all of the data files to be imported.

  You can specify the TRANSPORT_DATAFILES parameter multiple times in a parameter file specified with the PARFILE parameter if there are many data files.

• The TABLES parameter specifies the tables to be imported.

• The LOGFILE parameter specifies the file name of the log file to be written by the import utility.

After this statement executes successfully, check the import log file to ensure that no unexpected error has occurred.

When dealing with a large number of data files, specifying the list of data file names in the statement line can be a laborious process. It can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:

impdp user_name parfile='par.f'

For example, par.f might contain the following lines:

NETWORK_LINK=sourcedb 
TRANSPORTABLE=always
TRANSPORT_DATAFILES=
      '/u01/app/oracle/oradata/targetdb/emp.dbf'
      '/u01/app/oracle/oradata/targetdb/orders.dbf'
TABLES=hr.emp_ttbs,oe.orders_ttbs
LOGFILE=import.log

Note:

During the import, user-defined tablespaces might be temporarily made read/write for metadata loading. Ensure that no user changes are made to the data during the import. At the successful completion of the import, all user-defined tablespaces are made read/write.

See Also:

Oracle Database Utilities for information about using the import utility

Task 5   (Optional) Restore Tablespaces to Read/Write Mode

Make the tables that contain the tables being transported read/write again at the source database, as follows:

ALTER TABLESPACE emp_tsp READ WRITE;
ALTER TABLESPACE orders_tsp READ WRITE;

Converting Data Between Platforms Using the DBMS_FILE_TRANSFER Package

You can use the GET_FILE or PUT_FILE procedure in the DBMS_FILE_TRANSFER package to convert data between platforms during the data file transfer. When you use one of these procedures to move data files between the source platform and the target platform, each block in each data file is converted to the target platform's endianness.

This section uses an example to describe how to use the DBMS_FILE_TRANSFER package to convert a data file to a different platform. The example makes the following assumptions:

• The GET_FILE procedure will transfer the data file.

• The mytable.342.123456789 data file is being transferred to a different platform.

• The endianness of the source platform is different from the endianness of the target platform.

• The global name of the source database is dbsa.example.com.

• Both the source database and the target database use Oracle Automatic Storage Management (Oracle ASM).

Complete the following steps to convert the data file by transferring it with the GET_FILE procedure:

1. Connect to the source database as an administrative user that can create directory objects.

   See "Connecting to the Database with SQL*Plus" for instructions.

2. Create a directory object for the directory that contains the data file that will be transferred to the target database.

   For example, to create a directory object named sales_dir_source for the +data/dbsa/datafile directory, execute the following SQL statement:

   CREATE OR REPLACE DIRECTORY sales_dir_source 
      AS '+data/dbsa/datafile';
   

   The specified file system directory must exist when you create the directory object.

3. Connect to the target database as an administrative user that can create database links, create directory objects, and run the procedures in the DBMS_FILE_TRANSFER package.

   See "Connecting to the Database with SQL*Plus" for instructions.

4. Create a database link from the target database to the source database.

   The connected user at the source database must have read privilege on the directory object you created in Step 2.

   See "Creating Database Links".

5. Create a directory object to store the data files that will be transferred to the target database.

   The user at the local database who will run the procedure in the DBMS_FILE_TRANSFER package must have write privilege on the directory object.

   For example, to create a directory object named sales_dir_target for the +data/dbsb/datafile directory, execute the following SQL statement:

   CREATE OR REPLACE DIRECTORY sales_dir_target 
     AS '+data/dbsb/datafile';
   

6. Run the GET_FILE procedure in the DBMS_FILE_TRANSFER package to transfer the data file.

   For example, run the following procedure to transfer the mytable.342.123456789 data file from the source database to the target database using the database link you created in Step 4:

   BEGIN
     DBMS_FILE_TRANSFER.GET_FILE(
       source_directory_object      => 'sales_dir_source',
       source_file_name             => 'mytable.342.123456789',
       source_database              => 'dbsa.example.com',
       destination_directory_object => 'sales_dir_target',
       destination_file_name        => 'mytable');
   END;
   /
   

Converting Data Between Platforms Using RMAN

When you use the RMAN CONVERT command to convert data, you can either convert the data on the source platform after running Data Pump export, or you can convert it on the target platform before running Data Pump import. In either case, you must transfer the data files from the source system to the target system.

You can convert data with the following RMAN CONVERT commands:

• CONVERT DATAFILE

• CONVERT TABLESPACE

• CONVERT DATABASE

This section includes examples that use the CONVERT DATAFILE command and the CONVERT TABLESPACE command.

The following sections describe how to perform these conversions:

• Converting Tablespaces on the Source System After Export

• Converting Data Files on the Target System Before Import