Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

is it possible to export and import only constraints for a specific schema ?

JhilApr 9 2019 — edited Apr 15 2019

Dear Experts,

is it possible to export and import only constraints for a specific schema ?

There are n number of objects in a schema.

impdp process is very slow and running 16+ hours ..continuously.

We are planning to export only constraints and import them.

How do i EXCLUDE remaining objects like INDEXES, FUNCTIONS, TRIGGERS, etc .. (EXCEPT CONSTRAINTS)

I want to exclude all objects including table rows, only i want to export CONSTRAINTS.

Can anyone tell me the best approach to achieve this ?

DB Version is 12.2

FYI : I have noticed, library cache lock occurred after impdp process initiated.

Thanks in advance

Comments

Dean Gagne-Oracle Apr 9 2019

Data Pump has the ability to exclude anything - which includes everything else, or include anything, which excludes everything else.  SO...

expdp user/password schema=my_schema directory=dpump_dir dumpfile=my_constr.dmp include=constraint.

Hope this helps.

Dean

Jhil Apr 9 2019

Hi Dean,

expdp user/password schema=my_schema directory=dpump_dir dumpfile=my_constr.dmp include=constraint.

in that case, do I need to mention rows=N on above line.

we had .dmp which includes all objects including table rows

Dean Gagne-Oracle Apr 10 2019 — edited on Apr 10 2019

Hi,

If you say 'include=constraints' that is all that will be included.  No data will be included.  If you want data, then it will be a different command.  Let me know if you need data and I will think a bit more on that.

Dean

Jhil Apr 13 2019

Hi Dean,

We are not doing EXPDP and IMPDP frequently.

Last week we just started Schema Refresh activity.,

.dmp file size was 28 GB only

IMPDP took 18+ (which was incomplete) , client asked us to kill the IMPDP job.

I need best approach to perform schema refresh (which should not take long time)

I thought to take all schema objects except (indexes and constraints)

i would create indexes and constraints , once i done import all tables).

Please update me the best approach to achieve my targets in best way .

Can i do EXPDP/IMPDP  except indexes and constraints without any problem ?

Thanks in advance.

Dean Gagne-Oracle Apr 15 2019

So are you trying to move a schema from one database to another?   When you say it takes '18+'  is that hours? minutes?   I don't know what your schema has for objects so it is difficult to tell.   You can certainly run the import in stages.  Can you stage it on another database and see how long it will take to do the complete import?    If you add

metrics=y

this will tell you how much time each object took to import.   Then you may be able to make decisions on how to get it over there.   I assume the export is done and there isn't any issue with that.  The only issue is with how long it takes to import.  There are lots of questions here and I'm not sure I have all/any of the answers.    Getting a practice run may help a lot when determining what is going on and how to manage this.

Dean

Jhil Apr 15 2019

Hi Dean

EXPDP was completed.

IMPDP took 18+ hrs, but not completed. We killed impdp job

This is 12.2 RAC, MOS DOC says that 'should not use parallel=2 , if DB is rac and version is 12.2


The problem discussed on below thread, kindly check it and provide me some suggestions.

Need to check IMPDP is running or getting problem ?

Thanks in advance

1 - 6

Post Details

Added on Apr 9 2019
6 comments
10,105 views