-
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.
-
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.
-
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
-
Originally Posted by Unna
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.
-
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?
-
Originally Posted by Unna
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|