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

37 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. This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
    obat aborsi
    cara menggugurkan kandungan
    obat telat datang bulan
    obat penggugur kandungan

    ReplyDelete
  9. Here we present to introduce to all visitors in this great blog. and thank you for allowing me .

    cara menggugurkan kandungan
    obat aborsi
    tanda tanda kehamilan

    ReplyDelete
  10. 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
  11. 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
  12. 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
  13. 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
  14. This is a very good article material and it is very useful for us all. thank you . cara menggugurkan kandungan

    ReplyDelete