Skip to Main Content
  • Questions

  • Regarding Dmp File export without partition

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, vignesh.

Asked: November 02, 2016 - 10:21 pm UTC

Last updated: November 04, 2016 - 2:40 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

We have a table OA_ODIF_LEG which contains 1292 partitions(Range partition), We are exporting this table daily for business purpose. To reduce Time, We need to export that table without partition.

Please guide me on this

and Connor said...

I'm not sure what you mean by "without partition". You could look at:

1) exporting without data (eg, add: QUERY="WHERE 1=0" to the datapump filter)

2) just exporting 1 of the partitions (eg, tables=OA_ODIF_LEG:PAR1)

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

vignesh v, November 03, 2016 - 6:50 am UTC

Hi,

I mean, that table contains multiple partitions but while exporting it as a dmp file, the dmp file should have only one partition
Connor McDonald
November 04, 2016 - 2:40 am UTC

Do you meant that you still want all of the data ?

If so, I dont know of a way to do that natively with datapump. You could achieve in a few steps, ie

- extract the DDL from the dump file
- use the DDL create table without the partition clause
- load the dump file

few partitions export from a table

Sumit, November 03, 2016 - 1:26 pm UTC

You can export the limited partitions using Tables=Table_name.partition_name option that will only export selected partitions and optionally you can filter data also
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.