Differences between Oracle Data Pump export and traditional export (1)

Introduction: OracleDatabase 10 Gb introduces the latestData PumpData Dump technology allows DBAs or developers to quickly move database metadata (object definition) and Data to another oracle database. So traditional dataImport and ExportWhat is the difference between the technology and the new data pump technology?

Role of Data Pump Export Import (EXPDP and IMPDP)

1. implement logical backup and logical recovery.

2. move objects between database users.

3. move objects between databases

4. Implement table space migration. Differences between data pump export and traditional export:

Before 10 Gb, the traditional export and import respectively use the EXP tool and IMP tool, starting from 10 Gb, not only retain the original EXP and IMP tools, the Data Pump Export Import tools EXPDP and IMPDP are also provided. precautions when using EXPDP and IMPDP;

EXP and IMP are client-segment tool programs that can be used either on the client or on the server segment.

EXPDP and IMPDP are the tool programs on the server. They can only be used on the ORACLE server and cannot be used on the client.

IMP only applies to EXP export files, not EXPDP export files; IMPDP only applies to EXPDP export files, not EXP export files.

Data Pump export includes four modes: export table, export scheme, export tablespace, and export database.

EXPDP command line options

1. ATTACH

This option is used to establish an association between a customer session and an existing export role. Syntax:

ATTACH = [schema_name.] job_name

Schema_name is used to specify the scheme name, And job_name is used to specify the export job name. Note: If you use the ATTACH option, you cannot specify any other options in the command line except the connection string and ATTACH options. For example:

Expdp scott/tiger ATTACH = scott. export_job

2. CONTENT

This option is used to specify the content to be exported. The default value is ALL.

CONTENT = {ALL | DATA_ONLY | METADATA_ONLY}

When CONTENT is set to ALL, the object definition and ALL its data are exported. If it is DATA_ONLY, only the object data is exported. If it is METADATA_ONLY, only the object definition is exported.

Expdp scott/tiger DIRECTORY = dump DUMPFILE = a. dump

CONTENT = METADATA_ONLY

3. DIRECTORY

Specifies the directory where the dump and log files are located

DIRECTORY = directory_object

Directory_object is used to specify the directory object name. Note that DIRECTORY objects are created using the create directory statement, rather than the OS DIRECTORY.

Expdp scott/tiger DIRECTORY = dump DUMPFILE = a. dump

Create a directory:

Create directory dump as 'd: dump ';

Query the subdirectories created:

SELECT * FROM dba_directories;

4. DUMPFILE

Specifies the name of the dump file. The default name is expdat. dmp.

DUMPFILE = [directory_object:] file_name [,…]

Directory_object is used to specify the directory object name, And file_name is used to specify the dump file name. Note that if you do not specify directory_object, the export tool automatically uses the DIRECTORY object specified by the DIRECTORY option.

Expdp scott/tiger DIRECTORY = dump1 DUMPFILE = dump2: a. dmp

5. ESTIMATE

Used to estimate the disk space used by the exported table. The default value is BLOCKS.

EXTIMATE = {BLOCKS | STATISTICS}

When it is set to BLOCKS, oracle estimates the space occupied by the object based on the number of data BLOCKS occupied by the target object multiplied by the data block size. When it is set to STATISTICS, it estimates the space occupied by the object based on the recent statistical value.

Expdp scott/tiger TABLES = emp ESTIMATE = STATISTICS

DIRECTORY = dump DUMPFILE = a. dump

6. EXTIMATE_ONLY

Specifies whether to estimate only the disk space occupied by the Export job. The default value is N.

EXTIMATE_ONLY = {Y | N}

When it is set to Y, the export function only estimates the disk space occupied by the object, and does not execute the export job. When it is N, it not only estimates the disk space occupied by the object, the export operation is also performed.

Expdp scott/tiger ESTIMATE_ONLY = y NOLOGFILE = y

7. EXCLUDE

This option is used to specify the release object type or related objects to be excluded when the operation is executed.

EXCLUDE = object_type [: name_clause] [,…]

Object_type is used to specify the object type to be excluded, and name_clause is used to specify the specific object to be excluded. EXCLUDE and INCLUDE cannot be used at the same time

Expdp scott/tiger DIRECTORY = dump DUMPFILE = a. dup EXCLUDE = VIEW

8. FILESIZE

Specifies the maximum size of the exported file. The default value is 0, indicating that the file size is unlimited)

9. FLASHBACK_SCN

Specifies the time when table data is exported for a specific SCN.

FLASHBACK_SCN = scn_value

Scn_value is used to identify the SCN value. FLASHBACK_SCN and FLASHBACK_TIME cannot be used at the same time.

Expdp scott/tiger DIRECTORY = dump DUMPFILE = a. dmp

FLASHBACK_SCN = 358523

10. FLASHBACK_TIME

Export table data at a specific time point

FLASHBACK_TIME = "TO_TIMESTAMP (time_value )"

Expdp scott/tiger DIRECTORY = dump DUMPFILE = a. dmp FLASHBACK_TIME =

"TO_TIMESTAMP ('25-08-2004 14:35:00 ', 'dd-MM-YYYY HH24: MI: ss ')"