Oracle Data Pump Technology detailed

The Oracle database 10g uses data pump technology that enables DBAs or developers to quickly move database metadata (object definitions) and data to another Oracle databases.

The role of Data Pump export import (EXPDP and IMPDP):

1, the implementation of logical backup and logical recovery.

2. Move objects between database users.

3. Moving objects between databases

4, realize the table space move.

Second, the data pump export import and traditional export import difference:

Prior to 10g, traditional export and import using exp tools and IMP tools, starting with 10g, increased data pump export import Tools EXPDP and IMPDP.

Exp and Imp are customer segment utility programs that can be used either on the client or on the server side.

EXPDP and IMPDP are service-side tools that they can use only on the Oracle server and cannot be used on the client side

IMP applies only to the EXP export file, not to the EXPDP export file, IMPDP only to the EXPDP export file, not to the EXP export file.

Data pump export includes export table, export scheme, export table space, export database 4 ways.

Three, EXPDP command-line options

1. ATTACH

This option is used to establish an association between a client session and an existing export action. The syntax is as follows

Attach=[schema_name.] Job_name

Schema_name is used to specify the scheme name, job_name to specify the export job name. Note that if you use the Attach option, you cannot specify any other options except for the connection string and the Attach option on the command line, as shown in the following example:

EXPDP newhappy/pdmcn.com Attach=scott.export_job

2. CONTENT

This option is used to specify what to export. The default value is all

Content={all | data_only | Metadata_only}

When the content is set to all, the object definition and all its data are exported. When data_only, only object data is exported, when metadata_only, only the object definition is exported

EXPDP newhappy/pdmcn.com Directory=dump Dumpfile=a.dump

Content=metadata_only

3. DIRECTORY

Specify the directory where dump files and log files are located

Directory=directory_object

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

EXPDP newhappy/pdmcn.com Directory=dump Dumpfile=a.dump

Create a directory:

CREATE DIRECTORY dump as ' d:dump ';

The query created those subdirectories:

SELECT * from Dba_directories;

4. DumpFile

Use to specify the name of the dump file, the default name is Expdat.dmp

Dumpfile=[directory_object:]file_name [,...]

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45390.htm

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

EXPDP newhappy/pdmcn.com DIRECTORY=DUMP1 dumpfile=dump2:a.dmp

5. Estimate

Specifies the disk space partition method used to estimate which table is being exported. The default value is blocks

Extimate={blocks | STATISTICS}

When set to blocks, Oracle uses the number of blocks of data used by the target object multiplied by the size of the data block to estimate the space occupied by the object, and when set to statistics, estimate the object footprint according to the most recent statistic

EXPDP newhappy/pdmcn.com 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, and the default value is N

Extimate_only={y | N

When set to Y, the export effect estimates only the disk space occupied by the object, but does not perform an export job, when n, not only estimating the disk space occupied by the object, but also performing an export operation.

EXPDP newhappy/pdmcn.com estimate_only=y Nologfile=y

7. EXCLUDE

This option specifies the release of the object type or related object to exclude when performing an action

Exclude=object_type[:name_clause] [,...]

Object_type is used to specify the type of object to exclude, name_clause to specify the specific object to exclude. Exclude and include cannot be used concurrently

EXPDP newhappy/pdmcn.com directory=dump dumpfile=a.dup Exclude=view

8. FILESIZE

Specifies the maximum size of the exported file, which defaults to 0 (indicates file size is not limited)

9. Flashback_scn

Specify table data to export a specific SCN time

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 newhappy/pdmcn.com Directory=dump dumpfile=a.dmp

flashback_scn=358523

Flashback_time

Specify the export of table data for a specific point in time

Flashback_time= "To_timestamp (time_value)"

EXPDP newhappy/pdmcn.com directory=dump dumpfile=a.dmp flashback_time=

"To_timestamp (' 25-08-2004 14:35:00 ', ' dd-mm-yyyy HH24:MI:SS ')"

One. Full

Specify database schema export, default to N