Oracle11gRelease1 (11.1) DataPump export Mode

Oracle11gRelease1 (11.1) DataPump export mode. DataPump provides different export modes to detach different parts of the database. By entering

Oracle 11g Release 1 (11.1) Data Pump export mode. Data Pump provides different export modes to detach different parts of the database. By entering

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, the SYS, ORDSYS, and MDSYS system modes cannot be exported.

Full Export Mode

Use the FULL parameter to specify "FULL export ". A full export will unload the entire database. This mode requires you to have the EXP_FULL_DATABASE role.

Example 1: This example shows the FULL parameter. The dump file expfull. dmp is written to the export directory dpump_dir2.

Expdp hr DIRECTORY = dpump_dir2 DUMPFILE = expfull. dmpFULL= Y NOLOGFILE = y

Schema mode

Use the SCHEMAS parameter to specify "Export mode ". This is the default export mode. If you have the EXP_FULL_DATABASE role, you can specify a mode list, including the schema definitions of them and the modes granted by the system permissions. If you do not have the EXP_FULL_DATABASE role, you can only export your own mode.

The SYS mode cannot be exported. That is to say, the SYS mode 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 demonstrates the SCHEMAS parameter. Assume that the role EXP_FULL_DATABASE has been granted to the hr user, and more modes can be accessed.

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

Table mode

Use the TABLES parameter to specify "Table mode" for export. In this mode, only tables, partitions, and collections of their dependent objects can be detached.

If you specify both the TABLES parameter and the TRANSPORTABLE = ALWAYS parameter, 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 have the EXP_FULL_DATABASE role 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 uses the TABLES parameter to export three TABLES in hr mode: employees, jobs, and departments. Because the three tables are in the hr mode, the schema name before the table name can be omitted.

Expdp hr DIRECTORY = dpump_dir1 DUMPFILE = tables. dmpTABLES = employees, jobs, departments

Example 4: If the hr user has the role of EXP_FULL_DATABASE, use the TABLES parameter to export the partition. This example shows how to export the sales_q20172000 and sales_Q2_2000 partitions from sh mode.

Expdp hr DIRECTORY = dpump_dir1 DUMPFILE = tables_part.dmpTABLES = sh. sales: sales_q00002000, sh. sales: sales_Q2_2000

Example 5: If the sh mode has the EXP_FULL_DATABASE permission, table sales2 is partitioned and included in table space tbs2. (The tablespace tbs2 must be set to read-only in the source database .)

Expdp sh DIRECTORY = dpump_dir1 DUMPFILE = tto1.dmpTABLES = sh. sales2 TRANSPORTABLE = always

After the export is successful, you must copy the data file to the target database. Then, use the PARTITION_OPTIONS and REMAP_SCHEMA parameters to execute the import.

Impdp system PARTITION_OPTIONS = departition TRANSPORT_DATAFILES = oracle/dbs/tbs2 DIRECTORY = dpump_dir1 DUMPFILE = tto1.dmp REMAP_SCHEMA = sh: dp

Tablespace mode

Use the TABLESPACES parameter to specify 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 shows the TABLESPACES parameter. 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

Use the TRANSPORT_TABLESPACES parameter to specify 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 shows the TRANSPORT_TABLESPACES parameter. The tablespace tbs_1 is the tablespace to be moved. Assume that the tablespace tbs_1 already exists and is set to read-only.

Expdp hr DIRECTORY = dpump_dir1 DUMPFILE = tts. dmpTRANSPORT_TABLESPACES = tbs_1 TRANSPORT_FULL_CHECK = y LOGFILE = tts. log

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