how to import procedures and triggers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: how to import procedures and triggers

  1. #1
    Join Date
    Nov 2000
    Posts
    160

    Exclamation how to import procedures and triggers

    Dear all,

    I am trying to perform schemas refresh of my development database with my production database. However when I datapump my data using expdp and impdp I find that I am missing a lot of objects. This is my script to expdp and impdp in 10g R2:

    expdp / directory=PROD_DP_DIR dumpfile=prod_ref.dmp schemas=state,scott,employee parallel=2

    impdp / dumpfile=prord_ref.dmp directory=DEV_DP_DIR schemas=state,scott,employee logfile=dev.log parallel=2

    In production, I have these counts for my state schemas objects:
    OBJECT_TYPE COUNT(*)
    ------------------- ----------
    INDEX 16
    PROCEDURE 3
    TABLE PARTITION 65
    TABLE 129
    VIEW 27
    FUNCTION 1
    MATERIALIZED VIEW 4

    In development, my object count for this state schemas are:
    OBJECT_TYPE COUNT(*)
    ------------------- ----------
    INDEX 10
    TABLE PARTITION 65
    TABLE 129

    Now I need to import these objects which includes views, function, materialized views, procedures, and index. Please advise how I can do that?



    Thanks,
    Unna.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are expdp into prod_ref.dmp then you are impdp from prord_ref.dmp ... which is not the same file; that's why you are getting different stuff.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Posts
    160
    Dear PAVB,

    Thanks for your reply. Sorry I have typo on my impdp command, it should be the same dump file. the command for expdp and impdp are:

    From produciton dba:
    expdp / directory=PROD_DP_DIR dumpfile=prod_ref.dmp schemas=state,scott,employee parallel=2

    From development db:
    impdp / directory=DEV_DP_DIR dumpfile=prod_ref.dmp schemas=state,scott,employee logfile=dev.log parallel=2

    Any advice?


    Thanks,
    Unna

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Unna View Post
    Sorry I have typo on my impdp command, it should be the same dump file.
    ... and that's why we always suggest to copy/paste on a post rather than typing again

    So you want to import code only?

    You may want to check impdp parameter CONTENT=METADATA_ONLY, this is the same as ROWS=N in old-and-good imp utility.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Posts
    160
    Thanks PAVB!

    I was able to recover most of my objects withg CONTENT=METADATA_ONLY and indexes and index partitions using sqlfile.

    However, I understand why content=all doesn't import all the data, since by default the content is all which includes both data and metadata?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Unna View Post
    However, I understand why content=all doesn't import all the data, since by default the content is all which includes both data and metadata?
    Glad it worked out.

    I would say something went wrong during impdp content=all; most probably you will find what went wrong by checking impdp log file.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width