This discussion is archived
6 Replies Latest reply: Dec 6, 2012 1:28 PM by khallas301 RSS

expdp access_method=external_table

khallas301 Newbie
Currently Being Moderated
does access_method=external_table ingores constraints and indexes?

I have recently used it and can't find any indexes or constraints in export dmp file...

any thoughts?
  • 1. Re: expdp access_method=external_table
    User286067 Journeyer
    Currently Being Moderated
    khallas301 wrote:
    does access_method=external_table ingores constraints and indexes?

    I have recently used it and can't find any indexes or constraints in export dmp file...

    any thoughts?
    Show us your full expdp and impdp command and then we will see.
  • 2. Re: expdp access_method=external_table
    Dean Gagne Expert
    Currently Being Moderated
    Access_method just tells Data Pump how to load or unload data. It does not change anything related to metadata. Post your command and I'll take a look to see if there is something else in there that would cause metadata to change.

    Dean
  • 3. Re: expdp access_method=external_table
    khallas301 Newbie
    Currently Being Moderated
    directory=data_pump_refresh
    dumpfile=expdp%U.dmp
    parallel=4
    logfile=expdp.log
    schemas=ABC
    exclude=statistics,table:"IN(select tbl from ABC.list_of_tables)"
    access_method=EXTERNAL_TABLE
    flashback_time="SYSTIMESTAMP"
  • 4. Re: expdp access_method=external_table
    khallas301 Newbie
    Currently Being Moderated
    Import parameter file used:

    directory = data_pump_refresh
    dumpfile = expdp%U.dmp
    parallel = 4
    logfile = imdp.log
    exclude = db_link,tablespace_quota
    transform= segment_attributes:N
  • 5. Re: expdp access_method=external_table
    Dean Gagne Expert
    Currently Being Moderated
    Your export command is excluding some tables. Since you said to exclude these tables, the dependent objects will not be exported either. Can you tell us what tables the missing indexes and constraints are on, and can you tell us which tables are in your exclude table list? If they are the same, then the exclude is removing them. When you say exclude=table, then any path with 'TABLE' in the path will be excluded. If all you wanted to do was exclude the table, then you need to be more specific. Here is an example:

    If you say exclude=table ... then all of these will be excluded.

    SCHEMA_EXPORT/TABLE/DEPTABLE_OBJNUM
    SCHEMA_EXPORT/TABLE/POST_DATA_TABLE
    SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX
    SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
    SCHEMA_EXPORT/TABLE/CLUSTERING
    SCHEMA_EXPORT/TABLE/AUDIT_OBJ
    SCHEMA_EXPORT/TABLE/RLS_CONTEXT
    SCHEMA_EXPORT/TABLE/FGA_POLICY
    SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
    SCHEMA_EXPORT/TABLE/INDEX/STATISTICS
    SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
    SCHEMA_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS
    SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
    SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
    SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
    SCHEMA_EXPORT/TABLE/POST_INSTANCE/GRANT
    SCHEMA_EXPORT/TABLE/TABLE_OBJNUM
    SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
    SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION
    SCHEMA_EXPORT/TABLE/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT
    SCHEMA_EXPORT/TABLE/RLS_POLICY
    SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
    SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX
    SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
    SCHEMA_EXPORT/TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT
    SCHEMA_EXPORT/TABLE/POST_INSTANCE/INSTANCE_CALLOUT
    SCHEMA_EXPORT/TABLESPACE_QUOTA
    SCHEMA_EXPORT/TABLE/GRANT
    SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY
    SCHEMA_EXPORT/TABLE/INDEX
    SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX
    SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    SCHEMA_EXPORT/TABLE/INDEX/TABLE
    SCHEMA_EXPORT/TABLE/INSTANCE_CALLOUT
    SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT
    SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY_OBJNUM
    SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    SCHEMA_EXPORT/TABLE/TRIGGER
    SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
    SCHEMA_EXPORT/TABLE/POST_INSTANCE
    SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA
    SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
    SCHEMA_EXPORT/TABLE/INDEX/INDEX
    SCHEMA_EXPORT/TABLE/CONSTRAINT
    SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX
    SCHEMA_EXPORT/TABLE/INDEX/DOMIDX_OBJNUM
    SCHEMA_EXPORT/TABLE
    SCHEMA_EXPORT/TABLE/RLS_GROUP
    SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX
    SCHEMA_EXPORT/TABLE/INDEX/TABLE_DATA
    SCHEMA_EXPORT/TABLE/NTABLE_OBJNUM
    SCHEMA_EXPORT/TABLE/TABLE
    SCHEMA_EXPORT/TABLE/RADM_POLICY
    SCHEMA_EXPORT/TABLE/TABLE_DATA
    SCHEMA_EXPORT/TABLE/GRANT/WITH_GRANT_OPTION
    SCHEMA_EXPORT/TABLE/COMMENT
    SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
    SCHEMA_EXPORT/TABLE/STATISTICS
    SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT

    If you only want the table excluded and not the dependent objects, then say this:

    exclude=SCHEMA_EXPORT/TABLE/TABLE

    What you supply as the path is what will be excluded.

    Hope this makes sense.

    Dean
  • 6. Re: expdp access_method=external_table
    khallas301 Newbie
    Currently Being Moderated
    Thanks Dean.. that really makes sense and like to believe it.....
    but constraints and index which aren't dependent on excluded objects where also lost...

    anyways I have closed that project and continues with next mystery journey/task within Oracle..
    Once again thanks to you and rjamya for your time and efforts in spreading Oracle knowledge...

    keep good work continue..