Oracle Data Pump Export import differs from traditional export import

Let's take a look at the Oracle data pump Export Import Example

1, first set up a directory:

Create directory directory name as ' a directory on the database server ', such as:
Create directory alias as ' d:\ server directory name ';
Put the imported or exported files in this directory

2, Export and import

In SID=ORCL, export the DMP account to test, and import the DMP account to test for example.

To export data from SFZ:
EXPDP TEST/TEST@ORCL directory= alias dumpfile= export filename

Import into Test:
IMPDP TEST/TEST@ORCL directory= alias dumpfile= export file name. DMP
Import everywhere user name is not the same, do a mapping, the same time, do not have to write
Remap_schema=test:test1

The difference between pump export import and traditional export import

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. Data pump export import differs from traditional export import:

Prior to 10g, traditional export and import using exp tools and IMP tools, starting from 10g, not only retained the original exp and IMP tools, but also provided data pump export Import Tools EXPDP and IMPDP. Matters needing attention when using EXPDP and IMPDP;

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

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.

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 Scott/tiger 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 Scott/tiger 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 Scott/tiger 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 [,...]

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 Scott/tiger 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 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, 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 Scott/tiger 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 Scott/tiger 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 Scott/tiger 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 Scott/tiger 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

Full={y | N

When Y, the identity performs the database export.

Help

Specifies whether to display help information for the EXPDP command-line option, which defaults to n

When set to Y, help information for the export option is displayed.

EXPDP help=y

INCLUDE

Specify the types of objects and related objects to include when exporting

INCLUDE = Object_type[:name_clause] [,...]

Job_name

Specifies the name of the function to export, default to Sys_xxx

Job_name=jobname_string

LOGFILE

Specifies the name of the export log file file, with the default name Export.log

Logfile=[directory_object:]file_name

Directory_object is used to specify the directory object name, file_name to specify the export log filename. If you do not specify Directory_object. The export action automatically uses the appropriate option values for your directory.

EXPDP Scott/tiger directory=dump dumpfile=a.dmp logfile=a.log

Network_link

Specifies the database chain name, and you must set this option if you want to export the remote database object to a dump file in a local routine.

Nologfile

This option specifies that the export log file is prevented from being generated, and the default value is N.

PARALLEL

Specifies the number of parallel processes that perform the export operation, with a default value of 1

Parfile

Specify the name of the exported parameter file

Parfile=[directory_path] File_name

QUERY

Used to specify where conditions for filtering exported data

Query=[schema.] [TABLE_NAME:] Query_clause

Schema is used to specify the scenario name, table_name to specify the table name, and query_clause to specify the conditional restriction clause. The query option cannot be used in conjunction with options such as Connect=metadata_only,extimate_only,transport_tablespaces.

EXPDP Scott/tiger Directory=dump dumpfiel=a.dmp

Tables=emp query= ' WHERE deptno=20 '

SCHEMAS.

This scenario is used to specify the execution scenario schema export, which defaults to the current user scenario.

STATUS

Specifies the detailed status of the export action process, with a default value of 0

TABLES

Specify table Schema Export

Tables=[schema_name.] table_name[:p artition_name][,...]

Schema_name is used to specify the scenario name, table_name to specify the exported table name, partition_name to specify the partition name to export.

Tablespaces

Specify the list of table spaces to export

Transport_full_check

This option specifies how to check the relationship between the moved tablespace and the unbound table space, by default N.

When set to Y, the export action checks the full association of the table space directly, and displays an error message if the tablespace or the tablespace in which the index is located has only one tablespace moved. When set to N, the export only checks for single-ended dependencies, and if you move the tablespace of the index, but the table space is not moved, an error message is displayed, and if the table space in which the move table is located, the table space for the index is not moved.

Transport_tablespaces

Specifies that the table space mode export is performed

VERSION

Specifies the database version of the exported object, the default value is compatible.

version={compatible | LATEST | Version_string}

When compatible, the object metadata is generated according to the initialization parameter compatible, and when latest, object metadata is generated from the actual version of the database. Version_string is used to specify the database version string. Call EXPDP

When using the EXPDP tool, its dump file can only be stored in the corresponding OS directory of the directory object, not directly to the OS directory where the dump file resides. Therefore, when you use the EXPDP tool, you must first establish a directory object. And you need to grant database users permission to use directory objects.

CREATE DIRECTORY dump dir as ' d:dump ';

GRANT READ, Wirte on DIRECTORY Dump_dir to Scott;

1. Export table

EXPDP Scott/tiger Directory=dump_dir dumpfile=tab.dmp tables=dept,emp

2. Export scheme

EXPDP Scott/tiger Directory=dump_dir dumpfile=schema.dmp

Schemas=system,scott

3. Export Table Space

EXPDP System/manager Directory=dump_dir dumpfile=tablespace.dmp

Tablespaces=user01,user02

4. Export Database

EXPDP System/manager Directory=dump_dir dumpfile=full.dmp full=y