SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> sho parameter def NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SQL> alter session set current_schema = usr_dummy; Session altered. SQL> CREATE TABLE x (part_key varchar2(5),ID NUMBER, text VARCHAR2(30)) PARTITION BY LIST(part_key) (PARTITION p1 VALUES ('PART1'), PARTITION p2 VALUES ('PART2') ); Table created. CREATE UNIQUE INDEX ix ON x (ID, part_key) LOCAL; Index created. SQL> SELECT * FROM dba_segments WHERE owner = 'USR_DUMMY' AND segment_name IN ('X','IX'); no rows selected SQL> INSERT INTO x VALUES ('PART1',1,'test'); 1 row created. SQL> INSERT INTO x VALUES ('PART2',1,'test'); 1 row created. SQL> commit; Commit complete. SQL> set linesize 1024; SQL> column owner format a10 SQL> column SEGMENT_NAME format a10 SQL> column PARTITION_NAME format a10 SQL> SELECT owner,segment_name,partition_name,segment_type,segment_subtype,bytes,blocks,EXTENTS FROM dba_segments WHERE owner = 'USR_DUMMY' AND segment_name IN ('X','IX'); OWNER SEGMENT_NA PARTITION_ SEGMENT_TYPE SEGMENT_SU BYTES BLOCKS EXTENTS ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- USR_DUMMY IX P1 INDEX PARTITION ASSM 65536 8 1 USR_DUMMY X P2 TABLE PARTITION ASSM 8388608 1024 1 USR_DUMMY IX P2 INDEX PARTITION ASSM 65536 8 1 USR_DUMMY X P1 TABLE PARTITION ASSM 8388608 1024 1 SQL> create directory dir_dummy as '/tmp'; Directory created. SQL> DECLARE PROCEDURE pc_exp_tbl_using_dpmp(p_schema VARCHAR2, p_tbl_list VARCHAR2, p_exp_job_name VARCHAR2, p_dmp_file VARCHAR2, p_exp_log VARCHAR2, p_dbdir VARCHAR2) IS h1 NUMBER; v_job_state user_datapump_jobs.state%TYPE; BEGIN h1 := dbms_datapump.open(operation => 'EXPORT',job_mode => 'TABLE',job_name => p_exp_job_name); dbms_output.put_line(1); dbms_datapump.add_file(handle => h1, filename => p_dmp_file ,directory => p_dbdir, reusefile => 1); dbms_output.put_line(2); dbms_datapump.add_file(handle => h1, filename => p_exp_log, directory => p_dbdir, filetype => dbms_datapump.KU$_FILE_TYPE_LOG_FILE); dbms_output.put_line(3); dbms_datapump.metadata_filter(h1,'NAME_EXPR', ' IN ('''||p_tbl_list||''')'); DBMS_DATAPUMP.metadata_filter(h1,'SCHEMA_EXPR', 'IN ('''||p_schema||''')'); dbms_output.put_line(4); -- this should exclude logically the content of the second partition DBMS_DATAPUMP.DATA_FILTER(handle => h1,Name => 'SUBQUERY',value => 'where part_key = ''PART1''', table_name => 'X'); dbms_output.put_line(5); dbms_datapump.start_job(h1); dbms_datapump.wait_for_job(h1, v_job_state); EXCEPTION WHEN others THEN raise; END pc_exp_tbl_using_dpmp; BEGIN pc_exp_tbl_using_dpmp('USR_DUMMY','X','EXP','MYTEST','MYLOG','DIR_DUMMY'); END; / 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 PL/SQL procedure successfully completed. SQL> !cat /tmp/MYLOG.log Starting "SYS"."EXP": Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 16 MB 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 . . exported "USR_DUMMY"."X":"P1" 5.820 KB 1 rows . . exported "USR_DUMMY"."X":"P2" 5.796 KB 0 rows Master table "SYS"."EXP" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.EXP is: /tmp/MYTEST.dmp Job "SYS"."EXP" successfully completed at Fri May 14 21:38:45 2021 elapsed 0 00:00:10 SQL> drop table x purge; Table dropped. SQL> SELECT owner,segment_name,partition_name,segment_type,segment_subtype,bytes,blocks,EXTENTS FROM dba_segments WHERE owner = 'USR_DUMMY' AND segment_name IN ('X','IX'); no rows selected SQL> DECLARE PROCEDURE pc_imp_tbl_using_dpmp(p_schema VARCHAR2, p_exp_job_name VARCHAR2, p_dmp_file VARCHAR2, p_exp_log VARCHAR2, p_dbdir VARCHAR2) IS h1 NUMBER; v_job_state user_datapump_jobs.state%TYPE; BEGIN h1 := dbms_datapump.open(operation => 'IMPORT',job_mode => 'TABLE',job_name => p_exp_job_name); dbms_output.put_line(1); dbms_datapump.add_file(handle => h1, filename => p_dmp_file ,directory => p_dbdir, reusefile => 1); dbms_output.put_line(2); dbms_datapump.add_file(handle => h1, filename => p_exp_log, directory => p_dbdir, filetype => dbms_datapump.KU$_FILE_TYPE_LOG_FILE); dbms_output.put_line(3); dbms_output.put_line(4); DBMS_DATAPUMP.metadata_filter(h1,'SCHEMA_EXPR', 'IN ('''||p_schema||''')'); dbms_output.put_line(5); dbms_datapump.start_job(h1); dbms_datapump.wait_for_job(h1, v_job_state); EXCEPTION WHEN others THEN raise; END pc_imp_tbl_using_dpmp; BEGIN pc_imp_tbl_using_dpmp('USR_DUMMY','IMP_JB','MYTEST','MYLOG_IMP','DIR_DUMMY'); END; / 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 PL/SQL procedure successfully completed. SQL> !cat /tmp/MYLOG_IMP.log Master table "SYS"."IMP_JB" successfully loaded/unloaded Starting "SYS"."IMP_JB": Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "USR_DUMMY"."X":"P1" 5.820 KB 1 rows . . imported "USR_DUMMY"."X":"P2" 5.796 KB 0 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Job "SYS"."IMP_JB" successfully completed at Fri May 14 21:47:35 2021 elapsed 0 00:00:01 SQL> SELECT owner,segment_name,partition_name,segment_type,segment_subtype,bytes,blocks,EXTENTS FROM dba_segments WHERE owner = 'USR_DUMMY' AND segment_name IN ('X','IX'); OWNER SEGMENT_NA PARTITION_ SEGMENT_TYPE SEGMENT_SU BYTES BLOCKS EXTENTS ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- USR_DUMMY X P2 TABLE PARTITION ASSM 8388608 1024 1 USR_DUMMY IX P1 INDEX PARTITION ASSM 65536 8 1 USR_DUMMY IX P2 INDEX PARTITION ASSM 65536 8 1 USR_DUMMY X P1 TABLE PARTITION ASSM 8388608 1024 1 -- we see here that by default segments are created for empty partitions (I don't think it is a good idea this as default behaviour) SQL> drop table x purge; Table dropped. SQL> SELECT owner,segment_name,partition_name,segment_type,segment_subtype,bytes,blocks,EXTENTS FROM dba_segments WHERE owner = 'USR_DUMMY' AND segment_name IN ('X','IX'); no rows selected -- now I use DBMS_DATAPUMP.metadata_transform(h1,'SEGMENT_CREATION',0); SQL> DECLARE PROCEDURE pc_imp_tbl_using_dpmp(p_schema VARCHAR2, p_exp_job_name VARCHAR2, p_dmp_file VARCHAR2, p_exp_log VARCHAR2, p_dbdir VARCHAR2) IS h1 NUMBER; v_job_state user_datapump_jobs.state%TYPE; BEGIN h1 := dbms_datapump.open(operation => 'IMPORT',job_mode => 'TABLE',job_name => p_exp_job_name); dbms_output.put_line(1); dbms_datapump.add_file(handle => h1, filename => p_dmp_file ,directory => p_dbdir, reusefile => 1); dbms_output.put_line(2); dbms_datapump.add_file(handle => h1, filename => p_exp_log, directory => p_dbdir, filetype => dbms_datapump.KU$_FILE_TYPE_LOG_FILE); dbms_output.put_line(3); dbms_output.put_line(4); DBMS_DATAPUMP.metadata_filter(h1,'SCHEMA_EXPR', 'IN ('''||p_schema||''')'); DBMS_DATAPUMP.metadata_transform(h1,'SEGMENT_CREATION',0); dbms_output.put_line(5); dbms_datapump.start_job(h1); dbms_datapump.wait_for_job(h1, v_job_state); EXCEPTION WHEN others THEN raise; END pc_imp_tbl_using_dpmp; BEGIN pc_imp_tbl_using_dpmp('USR_DUMMY','IMPJOB','MYTEST','MYLOG__IMP','DIR_DUMMY'); END; / 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 PL/SQL procedure successfully completed. SQL> !cat /tmp/MYLOG__IMP.log Master table "SYS"."IMPJOB" successfully loaded/unloaded Starting "SYS"."IMPJOB": Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "USR_DUMMY"."X":"P1" 5.820 KB 1 rows . . imported "USR_DUMMY"."X":"P2" 5.796 KB 0 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Job "SYS"."IMPJOB" successfully completed at Fri May 14 21:59:08 2021 elapsed 0 00:00:01 SQL> SELECT owner,segment_name,partition_name,segment_type,segment_subtype,bytes,blocks,EXTENTS FROM dba_segments WHERE owner = 'USR_DUMMY' AND segment_name IN ('X','IX'); OWNER SEGMENT_NA PARTITION_ SEGMENT_TYPE SEGMENT_SU BYTES BLOCKS EXTENTS ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- USR_DUMMY IX P1 INDEX PARTITION ASSM 65536 8 1 USR_DUMMY X P1 TABLE PARTITION ASSM 8388608 1024 1 SQL>