how to import procedures and triggers

[Unna]

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.

[PAVB]

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.

[Unna]

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

[PAVB]

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.

[Unna]

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?

[PAVB]

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.