14 Replies Latest reply: Jul 16, 2013 1:41 AM by yxes2013 RSS

    impdp

    yxes2013

      Hi all,

       

      aix 6.1

      11.2.0.3

       

      I have an expdp dump from prod  to be imported to our test database.

      I have imported it using impdp, but to my surprise the tables were imported but  lots of indexes were not created? even If I have used TRANSFORM=SEGMENT_ATTRIBUTES:N just to use the default USERS tablespace.

       

      How do I import the indexes separately, skipping the tables and other objects?

       

      Thanks a lot,

        • 1. Re: impdp
          sb92075

          You repeatedly ask Read The Fine Manual questions & expect us to spoon feed you!

           

          impdp help=yes

          • 2. Re: impdp
            asahide

            Hi, INCLUDE=INDEX Regards,

            • 3. Re: impdp
              Karan

              did you try INCLUDE=INDEX:"LIKE 'EMP%' "  ?

              • 4. Re: impdp
                yxes2013

                Thanks all ....do I need to put EXCLUDE=TABLES? so it wont hit error for existing tables?

                 

                I still got lots of errors:

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."TMPCREJIDX" creation failed

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."TMPOREJIDX" creation failed

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."IBFTIDX1" creation failed

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."IBFTIDX2" creation failed

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."IBFTIDX3" creation failed

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."STATRAN_IX" creation failed

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."REJIDX" creation failed

                ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"BATCHDBA"."PP_ESH_PK" creation failed

                ..more...

                • 5. Re: impdp
                  Karan

                  I have to test it im not sure...Actually data pump ignores dependent objects if the base object exists, but they will load the data. Correct your errors  it shall import the data, for indexes :=

                   

                  impdp / DIRECTORY=data_pump_dir DUMPFILE=d1.dmp TABLE_EXISTS_ACTION=APPEND INCLUDE=INDEX

                  • 6. Re: impdp
                    Richard Harrison .

                    Hi,

                    The stats failed because the index failed to create - what was the error - you don't show that?

                     

                    Regards,

                    Harry

                     

                    http://dbaharrison.blogspot.com


                    • 7. Re: impdp
                      yxes2013

                      Hi,

                       

                      I think it is creating the index on a tablespace which is not existing coming from PROD. but I imported it using

                      tranform_segment=no so I want to used the dafault USERS. I do not understand why it still failed

                      • 8. Re: impdp
                        Richard Harrison .

                        Hmm,

                        I would have thought that would have worked. Have you tried also adding remap_tablespace=original:USERS ?

                        Is there anything unusual about those indexes in any way - did all of them fail or just some?

                         

                        Regards,

                        Harry

                        • 9. Re: impdp
                          yxes2013

                          Maybe we are implementing DATABASE VAULT? lots of constraints error  having been coming out due to this security enforcement tool It is very hard to troubleshoot what is causing the error. Thanks

                          • 10. Re: impdp
                            Richard Harrison .

                            Hi,

                            So are the indexes unique indexes and they fail to create due to data problems - is that was is happening? I've not really use database vault that much to know why that would be an issue but thats much more around security of data than creation of objects i would have thought.

                             

                            Can you post the import log where one of the indexes you list above fails to create so we can see the actual error code?

                             

                            Thanks,

                            Harry

                            • 11. Re: impdp
                              yxes2013

                              Hi Ric,

                               

                              The import log did not show any info about the create index command, but only the errors as listed above.

                              I tried to check the sqlfile for the dump and tried to run in sqlplus. But still same error. I am still searching all tablespace

                              name and change it to USERS.

                              • 12. Re: impdp
                                Dean Gagne-Oracle

                                > impdp / DIRECTORY=data_pump_dir DUMPFILE=d1.dmp TABLE_EXISTS_ACTION=APPEND INCLUDE=INDEX

                                 

                                This command is not 100% correct.  If you say include=index, then nothing else is included.  This means that data is not included.  So, saying TABLE_EXISTS_ACTION=APPEND doesn't mean anything since the data is not included.

                                 

                                Dean

                                • 13. Re: impdp
                                  Dean Gagne-Oracle

                                  I would run the import command but add:

                                   

                                  sqlfile=my_objects.sql

                                   

                                  Then you can look at the file my_objects.sql to see the create index statements.  You can then modify them as needed.  If there were no errors on creating the indexes, then I can't imagine why the indexes were not created.

                                   

                                  Dean

                                  • 14. Re: impdp
                                    yxes2013

                                    Hi Dean,

                                     

                                    There were errors when the indexes were created using the sqlfile because the tablespaces was not existing.

                                    My point is why did the transform segment did not work? I usually use this to force the tables and indexes to be created at the default assgined USERS ts.

                                     

                                     

                                    Thanks