DataPump の include、exclude フィルタ

Oracle 10g からメジャーバージョンで二世代も歴史があるユーティリティなのになぜかユーティリティマニュアルにほとんど説明がみつかりません。
結構多いですが(おそらく)もれのない最低限の範囲で一覧化しておきます。Oracle 12.2.0.1.0 の結果なのでバージョンによって名称や分類自体が異なる可能性があります。個別の環境において、添付した SQL で確認してください。

INCLUDE / EXCLUDE で使用する名前付きの単純パス名 (SIMPLE PATH)

expdp … EXCLUDE=TRIGGER …
impdp … INCLUDE=TABLE,SYNONYM …
などで指定する事前定義されているフィルタの名称のことで「名前付き(named = 指定可能)」と「指定不可」の2種類ものがある。以下の表は指定可能な単純パス名だけを一覧にしたものである。

テーブルモード向け

COMMENT 列にも記載があるが OBJECT_PATH 列を丁寧に見ていくと階層構造から 単純パス名 「CONSTRAINT」 に REF_CONSTRAINT が含まれていることを知ることができる。

SIMPLE_PATHOBJECT_PATHCOMMENTS
CONSTRAINTCONSTRAINTConstraints (including referential constraints)
(NOT NULL 制約、表の構成に必須な制約の exclude は不可)
TABLE/CONSTRAINT
TABLE_EXPORT/TABLE/CONSTRAINT
INDEXINDEXIndexes
TABLE/INDEX
TABLE_EXPORT/TABLE/INDEX
MATERIALIZED_ZONEMAPMATERIALIZED_ZONEMAPMaterialized zonemaps
TABLE/MATERIALIZED_ZONEMAP
TABLE_EXPORT/TABLE/MATERIALIZED_ZONEMAP
PROCDEPOBJPROCDEPOBJInstance procedural objects
POST_INSTANCE/PROCDEPOBJ
TABLE/POST_INSTANCE/PROCDEPOBJ
TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
REF_CONSTRAINTREF_CONSTRAINTReferential constraints
CONSTRAINT/REF_CONSTRAINT
TABLE/CONSTRAINT/REF_CONSTRAINT
TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
TRIGGERTRIGGERTriggers on the selected tables
TABLE/TRIGGER
TABLE_EXPORT/TABLE/TRIGGER
-- テーブルモード (TABLES=...) で使用できるパス名一覧
select regexp_substr(object_path,'([^/]+$)') simple_path, object_path, comments
  from TABLE_EXPORT_OBJECTS where named = 'Y' order by 1, length(object_path);

スキーマモード向け

OBJECT_PATH の部分は省略

SIMPLE_PATHCOMMENTS
ALTER_FUNCTIONRecompile functions
ALTER_PACKAGE_SPECRecompile package specifications
ALTER_PROCEDURERecompile procedures
ANALYTIC_VIEWAnalytic Views in the selected schemas
ATTRIBUTE_DIMENSIONAttribute Dimensions in the selected schemas
CLUSTERClusters in the selected schemas and their indexes
CONSTRAINTConstraints (including referential constraints)
DB_LINKPrivate database links in the selected schemas
(パブリックデータベース・リンクは全体モード)
DIMENSIONDimensions in the selected schemas
FUNCTIONFunctions and their dependent grants and audits
HIERARCHYHierarchies in the selected schemas
INDEXIndexes
INDEXTYPEIndextypes and their dependent grants and audits
JAVA_CLASSJava classes and their dependent grants and audits
JAVA_RESOURCEJava resources and their dependent grants and audits
JAVA_SOURCEJava sources and their dependent grants and audits
LIBRARYExternal procedure libraries in the selected schemas and their dependent grants and audits
MATERIALIZED_VIEWMaterialized views
MATERIALIZED_VIEW_LOGMaterialized view logs
MATERIALIZED_ZONEMAPMaterialized zonemaps
OPERATOROperators and their dependent grants and audits
PACKAGEPackages (both specification and body) and their dependent grants and audits
PACKAGE_BODYPackage bodies
PACKAGE_SPECPackage specifications
PROCDEPOBJInstance procedural objects
PROCEDUREProcedures and their dependent grants and audits
PROCOBJProcedural objects in the selected schemas
(※1 ジョブはここに含まれているがジョブだけを切り出す "名前付きのパス名" はない)
REFRESH_GROUPRefresh groups in the selected schemas
REF_CONSTRAINTReferential constraints
SEQUENCESequences in the selected schemas and their dependent grants and audits
SYNONYMPrivate synonyms in the selected schemas
(パブリックシノニムは全体モード)
TABLETables in the selected schemas and their dependent objects
TRIGGERTriggers
TYPETypes (both specification and body) and their dependent grants and audits
TYPE_BODYType bodies
TYPE_SPECType specifications
USERUser definitions for users associated with the selected schemas
VIEWViews and their dependent objects
XMLSCHEMAXMLSCHEMAS
XS_ACLXS Security ACLs
XS_DATA_SECURITYXS Data Security Policies
XS_SECURITY_CLASSXS Security Classes
-- スキーマモード( SCHEMAS=...) で使用できるパス名一覧
select regexp_substr(object_path,'([^/]+$)') simple_path, object_path, comments
  from SCHEMA_EXPORT_OBJECTS where named = 'Y' order by 1, length(object_path);

全体モード向け

OBJECT_PATH の部分は省略

SIMPLE_PATHCOMMENTS
ALTER_FUNCTIONRecompile functions
ALTER_PACKAGE_SPECRecompile package specifications
ALTER_PROCEDURERecompile procedures
ANALYTIC_VIEWAnalytic Views
ATTRIBUTE_DIMENSIONAttribute Dimensions
AUDIT_POLICYAudit Policy
AUDIT_POLICY_ENABLEAudit Policy Enable
CLUSTERClusters and their indexes
CONSTRAINTConstraints (including referential constraints)
CONTEXTApplication contexts
DB_LINKPrivate and public database links
DIMENSIONDimensions
DIRECTORYDirectories and their dependent grants and audits
FUNCTIONFunctions and their dependent grants and audits
HIERARCHYHierarchies
INDEXIndexes
INDEXTYPEIndextypes and their dependent grants and audits
JAVA_CLASSJava classes and their dependent grants and audits
JAVA_RESOURCEJava resources and their dependent grants and audits
JAVA_SOURCEJava sources and their dependent grants and audits
LIBRARYExternal procedure libraries and their dependent grants and audits
MATERIALIZED_VIEWMaterialized views
MATERIALIZED_VIEW_LOGMaterialized view logs
MATERIALIZED_ZONEMAPMaterialized zonemaps
OPERATOROperators and their dependent grants and audits
PACKAGEPackages (both specification and body) and their dependent grants and audits
PACKAGE_BODYPackage bodies
PACKAGE_SPECPackage specifications
PASSWORD_VERIFY_FUNCTIONThe password complexity verification function
PROCDEPOBJInstance procedural objects
PROCEDUREProcedures and their dependent grants and audits
PROCOBJProcedural objects in the selected schemas
(※1 ジョブはここに含まれているがジョブだけを切り出す "名前付きのパス名" はない)
PROFILEProfiles
REFRESH_GROUPRefresh groups
REF_CONSTRAINTReferential constraints
ROLERoles
ROLLBACK_SEGMENTRollback segments
SEQUENCESequences and their dependent grants and audits
SYNONYMPublic and private synonyms
(パブリックシノニムはここに含まれている)
TABLETables and their dependent objects
TABLESPACETablespace definitions
TABLESPACE_ILM_POLICYDefault ILM policies for tablespaces
TRIGGERTriggers
TYPETypes (both specification and body) and their dependent grants and audits
TYPE_BODYType bodies
TYPE_SPECType specifications
USERUser definitions
VIEWViews and their dependent objects
XMLSCHEMAXmlschemas
XS_ACLXS Security ACLs
XS_ACL_PARAMXS Security ACL Parameters
XS_DATA_SECURITYXS Data Security Policies
XS_NAMESPACEXS Security Namespace
XS_ROLEXS Security Roles
XS_ROLESETXS Security Rolesets
XS_ROLE_GRANTXS Security Role Grants
XS_SECURITY_CLASSXS Security Classes
XS_USERXS Security Users
DVPS_COMMAND_RULEDatabase Vault Command Rule definitions
DVPS_COMMAND_RULE_ALTSDatabase Vault Command Rule definitions for alter system
DVPS_DV_ACCTSDatabase Vault Controlling DVSYS/DVF Accounts
DVPS_DV_AUTH_DDLDatabase Vault Authorization for DDL
DVPS_DV_AUTH_DIAGDatabase Vault Authorization for Diagnostic
DVPS_DV_AUTH_DPDatabase Vault Authorization for Data Pump
DVPS_DV_AUTH_JOBDatabase Vault Authorization for Scheduler
DVPS_DV_AUTH_MAINTDatabase Vault Authorization for Maintenance
DVPS_DV_AUTH_PREPDatabase Vault Authorization for Preprocessor
DVPS_DV_AUTH_PROXYDatabase Vault Authorization for Proxy User
DVPS_DV_AUTH_TTSDatabase Vault Authorization for Transportable Data Pump
DVPS_DV_INDEX_FUNCDatabase Vault Index Functions List
DVPS_DV_ORADEBUGDatabase Vault Controlling ORADEBUG
DVPS_DV_POLICYDatabase Vault Policy definitions
DVPS_DV_POLICY_OBJ_CDatabase Vault Policy Command Rule definitions
DVPS_DV_POLICY_OBJ_C_ALTSDatabase Vault Policy Command Rule definitions for ALTER SYSTEM/SESSION
DVPS_DV_POLICY_OBJ_RDatabase Vault Policy Realm definitions
DVPS_DV_POLICY_OWNERDatabase Vault Policy Owner definitions
DVPS_FACTORDatabase Vault Factor definitions
DVPS_FACTOR_LINKDatabase Vault Factor Link definitions
DVPS_FACTOR_TYPEDatabase Vault Factor Type definitions
DVPS_IDENTITYDatabase Vault Identity definitions
DVPS_IDENTITY_MAPDatabase Vault Identity Map definitions
DVPS_REALMDatabase Vault Realm definitions
DVPS_REALM_AUTHORIZATIONDatabase Vault Realm Authorization definitions
DVPS_REALM_MEMBERSHIPDatabase Vault Realm Membership definitions
DVPS_ROLEDatabase Vault Role definitions
DVPS_RULEDatabase Vault Rule definitions
DVPS_RULE_SETDatabase Vault Rule Set definitions
DVPS_RULE_SET_MEMBERSHIPDatabase Vault Rules in a Rule Set definitions
DVPS_STAGING_REALM_MEMBERSHIPDatabase Vault Import Staging Realm Membership
-- 全体モード (FULL=Y) で使用できるパス名一覧
select regexp_substr(object_path,'([^/]+$)') simple_path, object_path, comments
  from DATABASE_EXPORT_OBJECTS where named = 'Y' order by 1, length(object_path);

(※1) SCHEDULER JOB のパス名

include / exclude で指定できるパス名は 「"名前付き (named)" パス名」のみで JOB は名前付きではない。しかし JOB の情報は 'PROCOBJ' に含まれている。
(~_EXPORT_OBJECTS とにらめっこしても PROCOBJ は導き出せない)

select named, regexp_substr(object_path,'([^/]+$)') simple_path, object_path, comments
 from DATABASE_EXPORT_OBJECTS  
 where object_path like '%SCHED%' or object_path like '%JOB%'
 order by 2, length(object_path);

DATABASE_EXPORT_OBJECTS

NAMEDSIMPLE_PATHOBJECT_PATHCOMMENTS
YDVPS_DV_AUTH_JOBDVPS_DV_AUTH_JOBDatabase Vault Authorization for Scheduler
YDVPS_DV_AUTH_JOBDVPS_DV_AUTH_GROUP/DVPS_DV_AUTH_JOBDatabase Vault Authorization for Scheduler
YDVPS_DV_AUTH_JOBDVPS_POLICY/DVPS_DV_AUTH_GROUP/DVPS_DV_AUTH_JOBDatabase Vault Authorization for Scheduler
YDVPS_DV_AUTH_JOBDATABASE_EXPORT/DVPS_POLICY/DVPS_DV_AUTH_GROUP/DVPS_DV_AUTH_JOBDatabase Vault Authorization for Scheduler
NJOBJOBJobs
NJOBSCHEMA/JOBJobs
NJOBDATABASE_EXPORT/SCHEMA/JOBJobs
SCHEDULERSCHEDULEROracle Scheduler

SCHEMA_EXPORT_OBJECTS の場合

NAMEDSIMPLE_PATHOBJECT_PATHCOMMENTS
NJOBJOBJobs
NJOBSCHEMA_EXPORT/JOBJobs

つまり 全体モード (DATABASE_EXPORT) とスキーマモード(SCHEMA_EXPORT) のときにジョブの情報は存在するが OBJECT TYPE によるフィルタはできない。
JOB の名称をあらかじめ知っているなら オブジェクトの名前でフィルタして抽出する。
知らない場合には (1) DBMS_METADATA を使用する(抽出元 のDB にアクセスできれば)。(2) 全体 (FULL=y) か スキーマモード (SCHEMA=…) で expdp されたものを impdp と SQLFILE オプション の組み合わせで JOB の部分だけを視認してエディタなどで切り出すというマニュアル方式で対応する。

いつかは名前付きになるのでしょう…か

進捗状況監視用ビュー

  • DBA_DATAPUMP_JOBS / USER_DATAPUMP_JOBS
  • DBA_DATAPUMP_SESSIONS
  • V$SESSION_LONGOPS

DataPump / include、exclude フィルタの関連トピックス

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle のライセンスがわからない…
Oracle Direct (ネットで聞いても最後はここで要確認)