Oracle 11g Release 1 (11.1) Data Pump export Mode

Content

Data Pump provides different export modes to detach different parts of the database. Specify the mode by entering appropriate parameters in the command line. The following are available export modes:

• Full Export Mode
• Schema mode
• Table mode
• Tablespace mode
• Transportable Tablespace mode
• Oracle 11g Release 1 (11.1) Data Pump import Mode

Note:

Many system modes cannot be exported because they do not have the user mode, including data and metadata that can be managed by Oracle. For example, you cannot exportSYS,ORDSYSAndMDSYSSystem Mode.

Full Export Mode

UseFULLThe parameter specifies "full export ". A full export will unload the entire database. This mode requires you to haveEXP_FULL_DATABASERole.

Example 1: This example showsFULLParameters. Dump FileExpfull. dmpExported DirectoryDpump_dir2.

expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=y NOLOGFILE=y

 

Schema mode

UseSCHEMASThe parameter specifies "Export mode ". This is the default export mode. If you haveEXP_FULL_DATABASERole, then you can specify a list of modes, including their schema definitions and the modes granted by the system permission. If you do not haveEXP_FULL_DATABASERole, so you can only export your own mode.

Cannot exportSYSMode. That is to say,SYSMode cannot be used as the source mode of the export job.

You cannot export a cross-mode reference unless the referenced mode is specified in the list of modes to be exported. For example, a table defined in a specified mode cannot be exported, but the trigger in the specified mode is not displayed. For tables in the specified mode dependency, the same is true for the definition of external types. In this case, the expected type definition already exists in the target database instance at the time of import.

Example 2: This example showsSCHEMASParameters. Assume thatHrUserEXP_FULL_DATABASERole to access more modes.

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe 

 

Table mode

UseTABLESThe parameter specifies "Table mode" for export. In this mode, only tables, partitions, and collections of their dependent objects can be detached.

If you specifyTABLESParameters andTRANSPORTABLE = ALWAYSParameter, then only the metadata of the object is detached. To move the actual data, copy the data file to the target database. In this way, the export is fast. If you move data files between versions or platforms, the data files may need to be processed by Oracle Recovery Manager (RMAN.

You must haveEXP_FULL_DATABASERole to specify tables that are not in your own mode. All specified tables must be in a separate mode. Note: In "Table mode", the column type definition cannot be imported. Therefore, the type definition already exists in the target database instance during export. In addition, you cannot export cross-mode references like "Export mode.

Example 3: This example shows how to useTABLESExport ParametersHrThree tables in the Mode:Employees,JobsAndAdministrative ments. Because the three tables areHrSo the schema name before the table name can be omitted.

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp

TABLES=employees,jobs,departments

Example 4: assumeHrThe user hasEXP_FULL_DATABASERole, useTABLESExport partition parameters. In this exampleShExport partitions in ModeSales_q20172000AndSales_Q2_2000.

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tables_part.dmp

TABLES=sh.sales:sales_Q1_2000,sh.sales:sales_Q2_2000

Example 5: SupposeShMode hasEXP_FULL_DATABASEPermission, tableSales2Partitioned and included in the tablespaceTbs2. (The tablespace must be set in the source database.Tbs2Read-only .)

expdp sh DIRECTORY=dpump_dir1 DUMPFILE=tto1.dmp

TABLES=sh.sales2 TRANSPORTABLE=always 

After the export is successful, you must copy the data file to the target database. Then, usePARTITION_OPTIONSAndREMAP_SCHEMAImport parameters.

impdp system PARTITION_OPTIONS=departition 

TRANSPORT_DATAFILES=oracle/dbs/tbs2 DIRECTORY=dpump_dir1 

DUMPFILE=tto1.dmp REMAP_SCHEMA=sh:dp

 

Tablespace mode

UseTABLESPACESThe parameter specifies the "tablespace mode ". In this mode, only tables in a specified tablespace can be detached. If a table is detached, its dependent objects are also detached. Both object metadata and data are detached. In table space mode, if any part of a table is in the specified set, the table and all its dependent objects will be exported. Authorized users obtain all tables. Unauthorized users can only obtain tables in their own mode.

Example 6: This example showsTABLESPACESParameters. Assume that the tablespace tbs_4, tbs_5, and tbs_6 already exist.

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp 

TABLESPACES=tbs_4, tbs_5, tbs_6

 

Transportable Tablespace mode

UseTRANSPORT_TABLESPACESThe parameter specifies the "transportable tablespace mode" for export. In this mode, only the metadata of the table in the specified tablespace set and the objects they depend on are exported. Copy the tablespace data file separately. Then, import the dump file containing metadata and specify the data file to be used.

This mode requires the specified table to be completely "self-contained ". That is to say, all storage segments of all tables defined in the tablespace and their indexes must be included in the set. If the Export Statement violates "self-sufficiency", all problems are identified and the export cannot be completed.

Once the export in this mode is stopped, it cannot be restarted. In addition, they cannot have parallel degrees greater than 1.

Encryption columns are not supported in this mode.

Note:

You cannot export transportable tablespaces and then import them to a lower-version database. The target database must be of the same version or higher than the source database.

Example 7: This example showsTRANSPORT_TABLESPACESParameters. TablespaceTbs_1Is the tablespace to be moved. Assume that the tablespaceTbs_1Already exists and is set to read-only.

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp

TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=y LOGFILE=tts.log

Oracle 11g Release 1 (11.1) Data Pump import Mode