Task 6: Import the Tablespace Set
To complete the transportable tablespaces operation, import the tablespace set.
To import the tablespace set:
- Run the Data Pump import utility as a user with
DATAPUMP_IMP_FULL_DATABASErole and import the tablespace metadata.
impdp user_name dumpfile=expdat.dmp directory=data_pump_dir transport_datafiles= 'c:\app\orauser\oradata\orawin\sales_101.dbf', 'c:\app\orauser\oradata\orawin\sales_201.dbf' remap_schema=sales1:crm1 remap_schema=sales2:crm2 logfile=tts_import.log Password: password
This example specifies the following Data Pump parameters:
DUMPFILEparameter specifies the exported file containing the metadata for the tablespaces to be imported.
DIRECTORYparameter specifies the directory object that identifies the location of the export dump file. You must create the
DIRECTORYobject before running Data Pump, and you must grant the
WRITEobject privileges on the directory to the user running the Import utility. See Oracle Database SQL Language Reference for information on the
In a non-CDB, the database creates the directory object
DATA_PUMP_DIRautomatically. Read and write access to this directory is automatically granted to the
DBArole, and thus to users
However, the database does not create the directory object
DATA_PUMP_DIRautomatically 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.
Oracle Database Utilities for information about the default directory when the
DIRECTORYparameter is omitted
Oracle Multitenant Administrator's Guide for more information about PDBs
TRANSPORT_DATAFILESparameter identifies all of the data files containing the tablespaces to be imported.
You can specify the
TRANSPORT_DATAFILESparameter multiple times in a parameter file specified with the
PARFILEparameter if there are many data files.
REMAP_SCHEMAparameter changes the ownership of database objects. If you do not specify
REMAP_SCHEMA, then all database objects (such as tables and indexes) are created in the same user schema as in the source database, and those users must already exist in the target database. If they do not exist, then the import utility returns an error. In this example, objects in the tablespace set owned by
sales1in the source database will be owned by
crm1in the target database after the tablespace set is imported. Similarly, objects owned by
sales2in the source database will be owned by
crm2in the target database. In this case, the target database is not required to have users
sales2, but must have users
Starting with Oracle Database 12c Release 2 (12.2), the Recovery Manager (RMAN)
RECOVERcommand can move tables to a different schema while remapping a table. See Oracle Database Backup and Recovery User’s Guide for more information.
LOGFILEparameter 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 runs successfully, all tablespaces in the set being copied remain in read-only mode. Check the import log file to ensure that no 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 as the data file list can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can run the Data Pump import utility as follows:
impdp user_name parfile='par.f'
par.fparameter file contains the following:
DUMPFILE=expdat.dmp DIRECTORY=data_pump_dir TRANSPORT_DATAFILES= 'C:\app\orauser\oradata\orawin\sales_101.dbf', 'C:\app\orauser\oradata\orawin\sales_201.dbf' REMAP_SCHEMA=sales1:crm1 REMAP_SCHEMA=sales2:crm2 LOGFILE=tts_import.log
Oracle Database Utilities for information about using the import utility
- If required, put the tablespaces into read/write mode on the target database.