Data Pump EXPDP : How to EXCLUDE table partition explained with example

Data pump will not do exclude for the table partitions. If you use exclude=table:”IN (‘EXAMPLE: EXAMPLE_P2’)” in the expdp, it will just ignore the exclude and it will perform the full table export with all the partitions for the table. To achieve this goal you have to use data pump API package using the DBMS_DATAPUMP.DATA_FILTER.
Also Refer this Table_data Option
See the example below.
In this example I have a table example with 2 partitions.
SQL> select partition_name, table_name from user_tab_partitions ;

PARTITION_NAME                 TABLE_NAME
------------------------------ ------------------------------
EXAMPLE_P2                     EXAMPLE
EXAMPLE_P1                     EXAMPLE  
I wanted to export only example_p2 partition. That means in this example I am going to exclude example_p1 partition. Please find the DBMS_DATAPUMP API code for this purpose below.
 Connect to sqlplus and execute below attached PL/SQL code
declare
  rvalue number;
begin
  rvalue := dbms_datapump.open (operation => 'EXPORT',
                            job_mode  => 'TABLE');

  dbms_datapump.add_file (handle    => rvalue,
                          filename  => 'EXP_PART_EXCLUDE.DMP',
                          directory => 'EXP_DIR',
                          filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

  dbms_datapump.add_file (handle    => rvalue,
                          filename  => 'EXP_PART_EXCLUDE.LOG',
                          directory => 'EXP_DIR',
                          filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter (handle => rvalue,
                                 name   => 'SCHEMA_EXPR',
                                 value  => 'IN (''SCOTT'')');

  dbms_datapump.metadata_filter (handle => rvalue,
                                 name   => 'NAME_EXPR',
                                 value  => 'IN (''EXAMPLE'')');

  dbms_datapump.data_filter (handle      => rvalue,
                             name        => 'PARTITION_LIST',
                             value       => '''EXAMPLE_P2''',
                             table_name  => 'EXAMPLE',
                             schema_name => 'SCOTT');

  dbms_datapump.start_job (handle => rvalue);
  dbms_datapump.detach (handle => rvalue);
end;
/
PL/SQL procedure successfully completed.

Check whether export dump is created in the exp_dir directory.

$ ls -ltr EXP_PART_EXCLUDE*
-rw-r-----   1 oracle   dba         4096 Jun 28 02:04 EXP_PART_EXCLUDE.DMP
-rw-r--r--   1 oracle   dba          137 Jun 28 02:05 EXP_PART_EXCLUDE.LOG

Verify the expdp logfile. See in the log file you can see it export only EXAMPLE_P2 partition only.

$ tail -f EXPDAT.LOG
Starting "SCOTT"."SYS_EXPORT_TABLE_04":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P2"             408.3 KB   45121 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_04 is:
  /home/oracle/SCOTT/EXPDAT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_04" successfully completed at 01:59:32

Suppose if you use exclude parameter in expdp, it will not consider the partitions. See below mentioned example.
expdp exclude=TABLES:"IN ('EXAMPLE:EXAMPLE_P1')" directory=exp_dir  dumpfile=scott_PART.dmp logfile=exp_scott_PART.log
$ tail -f exp_scott_PART.LOG
Starting "SCOTT"."SYS_EXPORT_TABLE_04":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P2"             208.8 KB   25027 rows
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P2"             408.3 KB   45121 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_04 is:
  /home/oracle/SCOTT/EXPDAT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_04" successfully completed at 03:23:32

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google