This discussion is archived
14 Replies Latest reply: Jul 15, 2013 11:41 PM by yxes2013 RSS

impdp

yxes2013 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

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

     

    impdp help=yes

  • 2. Re: impdp
    asahide Expert
    Currently Being Moderated

    Hi, INCLUDE=INDEX Regards,

  • 3. Re: impdp
    karan Pro
    Currently Being Moderated

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

  • 4. Re: impdp
    yxes2013 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 . Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 . Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 . Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    > 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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points