Shulou(Shulou.com)05/31 Report--

This article shows you how to analyze EXPDP/IMPDP various application scenarios, the content is concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

Data pump various scenarios specification documents

Creating a physical path.

Syntax: create or replace directory as 'backup path'

create or replace directory file_path as 'C:\software\database file'

Export syntax:

expdp username/password @ database connection identifier directory= path name dumpfile= file name of backup logfile= log name of output schemas= username of database to backup

Import syntax:

Impdp username/password @ database connection identifier directory= pathname dumpfile= file name of backup logfile= log name of output schemas= username of database to be backed up

1 By user:

derived: expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=file_path;

import: impdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=file_path;

2 By table name: (If exporting a table for ordinary users, users. Table name)

Export: expdp smsdb/123456@oracle11 tables=scott.emp,scott.dept directory=file_path dumpfile=2017.5.12.dmp

Import: impdp smsdb/123456@oracle11 tables=scott.emp,scott.dept directory=file_path dumpfile=2017.5.12.dmp

3 By Tablespace:

export: expdp system/manager DIRECTORY=file_path DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

import: impdp system/manager DIRECTORY=file_path DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

4 The entire library:

export: expdp system/manager DIRECTORY=file_path DUMPFILE=full.dmp FULL=y;

import: impdp system/manager DIRECTORY=file_path DUMPFILE=full.dmp FULL=y;

5 by query criteria

Export: expdp smsdb/123456@oracle11 directory=file_path dumpfile=new.dmp logfile=new.log schemas=smsdb query=\" where provider_id=\'2\'\";

Import: impdp smsdb/123456@oracle11 directory=file_path dumpfile=new.dmp logfile=new.log schemas=smsdb

6 high version export low version export plus version number to import:

Export: expdp system/oracle directory=file_path dumpfile=bha.dmp logfile=hj.log schemas=BHA exclude=statistics version=11.2.0.4.0

Import: impdp system/oracle directory=file_path dumpfile=bha.dmp logfile=hj.log schemas=BHA exclude=statistics

7 Guide table structure:

Export: expdp system/oracle directory=file_path dumpfile=LMEC_BHA.dmp logfile=new.log schemas=BHA content=metadata_only

Import: impdp system/oracle directory=file_path dumpfile=LMEC_BHA.dmp logfile=new.log schemas=BHA

Export only table structure: content=metadata_only Export only table data: content=data_only

8 By different tablespaces, by different users

Export: expdp sys/xxx directory=file_path dumpfile=bha.dmp logfile=hj.log schemas=BHA exclude=statistics

Import: impdp sys/xxx dumpfile=bha.dmp directory=file_path remap_schema=BHA:HFBHA remap_tablespace=BHA:HFBHA logfile=bha.log

9 Parallel export import

Derived: expdp system/oracle schemas=BHA file_path dumpfile=bha_%U.dmp parallel=3 logfile=bha.log

Import: impdp system/oracle schemas=BHA file_path dumpfile=bha_%U.dmp parallel=3 logfile=bha.log

10 Export Import Based on scn 10.1 Export Import Based on Table

flashback_scn

flashback_scn=scn_value

description

Specifies the system change number (SCN) that the export will use to enable the Flashback Query utility.

remarks

FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive

The FLASHBACK_SCN parameter applies only to Oracle Database flashback queries. It does not apply to flashback databases, flashback drops, or flashback archives

2、flashback_time

Flashback_time

flashback_time="TO_TIMESTAMP"

description

Find the SCN closest to the specified time and use this SCN to enable the Flashback utility. Export operations are performed using data consistent with this SCN.

remarks

FLASHBACK_TIME and FLASHBACK_SCN are mutually exclusive.

The FLASHBACK_TIME parameter applies only to Oracle Database flashback queries. It does not apply to flashback databases, flashback drops, or flashback data archives.

3、CONTENT

CONTENT

The default value is skip

description

If CONTENT=DATA_ONLY is set, the default value is APPLD, not SKIP

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

SKIP: Skip this table and move on to the next object. SKIP cannot be used if CONTENT has the DATA_ONLY parameter set.

APPEND: loads data into the object, but does not affect existing rows.

TRUNCATE: Delete existing rows and load all data.

REPLACE:drop an existing table, then create and load the data. REPLACE cannot be used if CONTENT is set to DATA_ONLY

remarks

Export table structure only: content=metadata_only

Export table data only: content=data_only

4、Compression

Compression

The default is to use meadata_oniy compression

description

ALL: Compresses both exported metadata and table data, resulting in the smallest and longest exported file.

DATA_ONLY: Compresses only table data. Export effect is obvious for large data volume, and smaller compressed file will be obtained than METADATA_ONLY method.

METADATA_ONLY: Only compresses metadata, not table data. The effect of this compression is generally not obvious, but it is faster.

NONE: Without any compression, the exported file is also the largest.

remarks

The above content is how to analyze EXPDP/IMPDP various application scenarios. Have you learned knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.