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

44 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. This comment has been removed by the author.

    ReplyDelete
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. Be that as it may, think about what, it isn't illegal to do that in many states.electric water transfer pump

    ReplyDelete
  14. 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
  15. 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
  16. 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
  17. 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