Oracleexpdp filtering and Parallelism

We know there are three ways to call the Data Pump. Here I will never use the command line, but the reasons for giving up the command line through the first script are as follows:

We know there are three ways to call the Data Pump. Here I will never use the command line, but the reasons for giving up the command line through the first script are as follows:

Preface:

We know there are three ways to call the Data Pump. Here, I will never use the command line but the first script method.

The reasons for dropping the command line are as follows:

① Operating system restrictions on the number of CLI characters
② An annoying Escape Character
③ Cross-platform reuse is not allowed
④ Official documents advocate script

(I) filtering objects/Data

(1) filter objects

INCLUDE

EXCLUDE and INCLUDE are used in the same way. Here we will talk about INCLUDE

Syntax

INCLUDE = object_type [: name_clause] [,...] object_type: TABLE, INDEX, CONSTRAINT, and GRANT are commonly used. For more information, see database_export_objects, schema_export_objects, and explain: SQL statement.

Related indexes, constraints, and so on will be exported together with great enthusiasm.

Example

Question: export all tables starting with emp

The parameter file is written as follows:

[Oracle@bogon ~]$ cat hr.parSCHEMAS=HRDUMPFILE=hr.dmpDIRECTORY=dmpdirLOGFILE=hr.logINCLUDE=TABLE:"like 'EMP%'"

In this file, INCLUDE can also be written like this

INCLUDE=TABLE:"IN (select table_name from user_tables where table_name like 'EMP%')"

(2) filter data

QUERY

Syntax

QUERY = [schema.][table_name:] query_clause

This parameter is mutually exclusive with the following:

CONTENT=METADATA_ONLYESTIMATE_ONLYTRANSPORT_TABLESPACES

Example

Question: export all employee information whose department number is 50 and whose employee number is greater than 128

The parameter file is written as follows:

[oracle@bogon ~]$ cat hr.parSCHEMAS=HRDUMPFILE=hr02.dmpDIRECTORY=dmpdirLOGFILE=hr02.logINCLUDE=TABLE:"IN ('EMPLOYEES','DEPARTMENTS')"QUERY=EMPLOYEES:"where employee_id>=128",DEPARTMENTS:"where department_id=20"

(Ii) parallel export

There are only three results for parallel settings: better, no change, and worse.

Whether the efficiency is improved does not depend on whether you have set the parameter of the Multi-bunker.

Parallelism requires costs, because before parallelism, server resources need to be comprehensively weighed, computing is parallel, and tasks are allocated.

PARALLEL setting is an art, because PARALLEL often needs to be considered together with FILESIZE and DUMPFILE.

The following is an example of Parallel Optimization provided by the official document:

expdp hr/hr FULL=y DUMPFILE=dpump_dir1:full1%U.dmp, dpump_dir2:full2%U.dmpFILESIZE=2G PARALLEL=3 LOGFILE=dpump_dir1:expfull.log JOB_NAME=expfull

In addition, the concurrency setting should not exceed 2 times the number of CPUs

sys@ORCL> show parameter cpuNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cpu_count                            integer     1By DBA_WaterBin2013-09-05GOOD Luck

The above is the content of Oracle expdp filtering and parallel. For more information, see PHP Chinese website (www.php1.cn )!