Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Berton.

Asked: January 17, 2023 - 3:04 pm UTC

Last updated: January 23, 2023 - 6:51 am UTC

Version: 19,0

Viewed 1000+ times

You Asked

Hey Guys,

We are attempting to export selective partitions of a table and would like to filter records for each partition. Would you please explain how we can achieve using one parameter file? Below is an example of the parameter file that we are using.

For example, I have a table in my schema ( mytable ) with 20 partiions, I want to export 2 partitions ( SYS_P9095 and SYS_P9096 ). The where clause must be different for each of the partitions, my question to you is how do I accomplish this ?

The query parameter in the current parameter file ( listed below ) applies to all partitions exported.

--Contents of parameter file
dumpfile=my_dump.dmp
logfile=my_dump.log
job_name=my_exp_dump
compression=all
parallel=4
cluster=N
tables='ORAGUY.MYTABLE:SYS_P9095', 'ORAGUY.MYTABLE:SYS_P9096'
query='ORAGUY.MYTABLE:"WHERE SELECT_FLAG <= 20"'



and Connor said...

One option would be to use the partition key definitions as predicates in your QUERY clause, eg

SQL> create table t
  2  partition by range ( object_id)
  3  (
  4    partition p1 values less than ( 20000 ),
  5    partition p2 values less than ( 60000 ),
  6    partition p3 values less than ( 80000 ),
  7    partition p4 values less than ( 100000 )
  8  )
  9  as select * from dba_objects
 10  where object_id is not null;

Table created.

parfile
=======
userid=mcdonac/xxxxxx@pdb21a
directory=temp
dumpfile=par.dmp
tables=t:p1,t:p4
query=t:"where ( object_id < 20000 and owner = 'SYS' ) or ( object_id >= 80000 and object_id < 100000 and owner = 'SCOTT' )"


C:\>expdp parfile=x:\temp\part.par

Export: Release 21.0.0.0.0 - Production on Wed Jan 18 15:30:43 2023
Version 21.7.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "MCDONAC"."SYS_EXPORT_TABLE_01":  mcdonac/********@pdb21a parfile=x:\temp\part.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MCDONAC"."T":"P1"                          1.661 MB   13356 rows
. . exported "MCDONAC"."T":"P4"                          17.17 KB       9 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TMP\PAR.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 18 15:30:49 2023 elapsed 0 00:00:05




You can see by using a standard SELECT that this still achieves partition elimination

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   901 |   124K|   142   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE OR|      |   901 |   124K|   142   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
|*  2 |   TABLE ACCESS FULL| T    |   901 |   124K|   142   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS' AND "OBJECT_ID"<20000 OR "OWNER"='SCOTT' AND
              "OBJECT_ID">=80000 AND "OBJECT_ID"<100000)



Rating

  (1 rating)

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

Comments

Fantastic !!!

BC, January 19, 2023 - 8:11 pm UTC

Thank you so much for this solution, it is simply amazing. I appreciate it Connor, you guys rock !!

Connor McDonald
January 23, 2023 - 6:51 am UTC

glad we could help

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.