6 Replies Latest reply: Dec 6, 2012 3:28 PM by khallas301 RSS

    expdp access_method=external_table

    khallas301
      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
          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
            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
              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
                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
                  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
                    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..