DATAPUMP如何導(dǎo)出時(shí)使用exclude排除表

151次閱讀

共計(jì) 3219 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

這篇文章主要介紹了 DATAPUMP 如何導(dǎo)出時(shí)使用 exclude 排除表，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

一、問題描述

有個(gè)小小需求，要從生產(chǎn)數(shù)據(jù)庫(kù)上導(dǎo)數(shù)據(jù)到測(cè)試庫(kù)，問題是有張大表（30G）不需要導(dǎo)出到測(cè)試環(huán)境，所以要使用到 expdp 中’exclude’參數(shù)。但是在使用過程中還是碰到一點(diǎn)點(diǎn) question。

二、操作過程

1. 根據(jù) expdp -help 信息中得知 exclude 參數(shù)的 for example 如下：

EXCLUDE

Exclude specific object types.

For example, EXCLUDE=SCHEMA: = HR”.

當(dāng)然 = 也可以替換成 in () 或 like ‘%..%’這種方式，如：exclude=index:”like‘emp%’，也可以只給 object types，如: exclude=VIEW,PACKAGE, FUNCTION

官方文檔給出的 Syntax 是

EXCLUDE=object_type[:name_clause] [, …]

按照上面的例子，我推斷出 exclude 參數(shù)針對(duì)表的使用

exclude=table:”=‘table name’”

2. 執(zhí)行語(yǔ)句報(bào)錯(cuò)，此處說明語(yǔ)法有問題，最終查詢到原來是由于沒有加轉(zhuǎn)義符。

[oracle@rac2 ~]$ expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log exclude=table: = WSCT_DATA compression=all

LRM-00116: syntax error at table: following =

3. 修改，增加轉(zhuǎn)義符后執(zhí)行成功

[oracle@rac2 ~]$ expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log EXCLUDE=TABLE:\ = \ WSCT_DATA\ \ compression=all

Export: Release 11.2.0.4.0 – Production on Thu Jul 13 10:17:50 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: log=cloan_20170713.log Location: Command Line, Replaced with: logfile=cloan_20170713.log

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting CLOAN . SYS_EXPORT_SCHEMA_02 : cloan/******** DIRECTORY=backup DUMPFILE=cloan_20170713.dmp logfile=cloan_20170713.log EXCLUDE=TABLE: = WSCT_DATA compression=all reuse_dumpfiles=true

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

… …

Dump file set for CLOAN.SYS_EXPORT_SCHEMA_02 is:

/backup/cloan_20170713.dmp

Job CLOAN . SYS_EXPORT_SCHEMA_02 successfully completed at Thu Jul 13 10:19:18 2017 elapsed 0 00:01:27

4. 導(dǎo)入到測(cè)試環(huán)境成功

[oracle@testdb backup]$ impdp cloancp/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan;

Import: Release 11.2.0.4.0 – Production on Thu Jul 13 10:45:30 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table CLOANCP . SYS_IMPORT_FULL_01 successfully loaded/unloaded

Starting CLOANCP . SYS_IMPORT_FULL_01 : cloancp/******** DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

……

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job CLOANCP . SYS_IMPORT_FULL_01 completed with 4 error(s) at Thu Jul 13 10:46:34 2017 elapsed 0 00:01:04

感謝你能夠認(rèn)真閱讀完這篇文章，希望丸趣 TV 小編分享的“DATAPUMP 如何導(dǎo)出時(shí)使用 exclude 排除表”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持丸趣 TV，關(guān)注丸趣 TV 行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!

正文完