Tuesday, September 27, 2011

MULTIPLE QUERIES WITH EXPDP DATA PUMP


Reason:
If you don't want to pump big tables you can use multiple QUERY predicate.
expdp  "'/ as sysdba'"   parfile=expdp.dat
or
expdp  system/manager@DB parfile=expdp.dat 

Note:
cat expdp.dat file

DUMPFILE="dat.dmp"
LOGFILE="exp_dat.log"
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=('RMS_ACQ_B')
QUERY='RMS_ACQ_B.RISKMAN_AUTH_TMP:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.MSG_HEAD_ARMS:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.RISKMAN_SLIPS_TMP:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.RISKMAN_SLP_FULL:"WHERE rownum<1"'
CONTENT=ALL
JOB_NAME='v1'

REUSE_DUMPFILES=Y #COMPATIBLE=11.2 CAN BE OVERWRITTEN
#COMPRESSION=ALL #COMPATIBLE=11.2

example:

[oracle@test ~]$ expdp SYSTEM/manager parfile=expdp.dat

Export: Release 11.2.0.2.0 - Production on Mon Sep 26 17:19:27 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Starting "RMS_PREV"."V2":  system/******** parfile=expdp.dat
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32.11 GB
Processing object type SCHEMA_EXPORT/USER
[...]
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "RMS_ACQ_B"."RISKMAN_AUTH_TMP"              16.21 KB       0 rows
. . exported "RMS_ACQ_B"."MSG_HEAD_ARMS"                 20.10 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_SLIPS_TMP"             14.89 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_SLP_FULL"              13.17 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_AUTH_TMP_NEW"          320.7 MB  723385 rows
 
 
If you need to  exclude TEST tables
(syntax note add \ if QUERY= not on Windows or not in parfile; on Linux must prefix with \ most punctuation charactersexample:expdp user/password QUERY='user.table:\"WHERE rownum\<1\"')

example:

expdp SYSTEM/manager DUMPFILE=dat.dmp EXCLUDE=table:\"like \'%TEST%\'\" QUERY=RMS_ACQ_B.RISKMAN_AUTH_TMP:\"WHERE rownum\<1\" LOGFILE=exp_dat.log SCHEMAS=RMS_ACQ_B

45 comments:

  1. I found many useful datapump tips in this page.
    http://www.acehints.com/p/site-map.html

    ReplyDelete
    Replies
    1. Hey there! Ive been following your weblog for a long time now and finally got the courage to go ahead and give you a shout out from Porter Tx! Just wanted to mention keep up the good work!
      Buy Sleeping Tablets Online

      Delete
    2. You can't just ask customers what they want and then try to give that to them. By the time you get it built, they'll want something new.

      Delete
    3. This is very interesting content! I have thoroughly enjoyed reading your points and have come to the conclusion that you are right about many of them. You are great.
      climbing shoe resole

      Delete
  2. yard sale sign Really you have done great job,There are may person searching about that now they will find enough resources by your post.I like this blog..

    ReplyDelete
  3. valium online
    Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me.

    ReplyDelete
  4. Thanks for sharing this info. It helped me during one of the request in office.

    ReplyDelete
  5. We want to reinvent the phone. What's the killer app? The killer app is making calls! It's amazing how hard it is to make calls on most phones. We want to let you use contacts like never before - sync your iPhone with your PC or mac.

    ReplyDelete
    Replies
    1. Florence is the most beautiful city with Italy... skill, architecture, cooking, beautiful countryside and good shopping destinations. So, you should definitely wear ones... and hit the street.
      dresses 2015

      Delete
    2. Excellent... Thanks a lot.

      Delete
  6. Oh, this is a magical post. It’s life the most majestic post there is on this planet right now. This is really good work from your side. I strongly recommend you keep doing this kind of work.
    buy stilnoct

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. To find an increased exposure for your products, Individuals expend cash to Buy Facebook Comments to very easily obtain exposure immediately. buy fb comments

    ReplyDelete
  9. To get a better hype to your facebook account, Individuals expend cash to Buy Facebook Followers to quickly find exposure on world wide web. buy 500 facebook followers

    ReplyDelete
  10. Hi, I am really happy to found such a helpful and fascinating post that is written in well manner. Thanks for sharing such an informative post. keep update your blog. R Programming Online Training

    ReplyDelete
  11. Mobdro has actually over-blog.com/ featured the majority of various qualities that official source any one of the other media application doesn't have DOWNLOAD MOBDRO INSTANTLY FOR ANDROID, IOS & WINDOWS Mobdro has in fact divided its on-line streaming Mobdro Mobdro offers the quick information concerning Mobdro App upcoming flicks.

    ReplyDelete
  12. This is a very good article material and it is very useful for us all. thank you . cara menggugurkan kandungan

    ReplyDelete
  13. Nhờ thành phần dưỡng chất và dược chất đa dạng như vậy mà công dụng của nhân sâm hàn quốc tại tphcm cũng vô cùng phong phú giúp con người bồi bổ sức khỏe nhan sam han quoc ngăn ngừa, điều trị nhiều thể bệnh khác nhau.

    ReplyDelete
  14. I will be pleased it absolutely was a satisfying knowledge to suit your needs. I do believe that you need to absolutely repeat at some time and with any luck, my partner and I should be able to ensure it is compared to that a single. Follow-up Cash Advance Carson Project Aid

    ReplyDelete
  15. Another job of the Facilitator-Coach is that of an information asset individual, helping team individuals to take in more about gathering elements, singular conduct and the aptitudes expected to end up more viable as a team and as people. Skillsfuture courses

    ReplyDelete
  16. Thank you very much for writing such an interesting article on this topic. This has really made me think and I hope to read more. odzyskiwanie danych Warszawa

    ReplyDelete
  17. Be that as it may, think about what, it isn't illegal to do that in many states.electric water transfer pump

    ReplyDelete
  18. There are a lot of blogs and articles out there on this topic, but you have acquired another side of the subject. This is reliable content thank you for sharing it. best selling

    ReplyDelete
  19. Good Post! Thank you so much for sharing this cool post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Java training in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Online Training

    ReplyDelete
  20. Microsoft Office 2010 Crack from the link given at the bottom · Then you must have to download Microsoft office Portable Microsoft Office 2010 Cracked

    ReplyDelete
  21. Christmas 2021 wishes, greetings, status and messages: · 1. Wishing you love and light in this challenging season. · 2. May this holiday season be .
    Romantic Christmas Messages

    ReplyDelete