advertisements
_____________________________________________________________________________________________________________________
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.
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
Therefore please use DBMS_DATAPUMP APIs to take the backup of desired table partitions
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment