How do I use selected columns with query parameter in Data Pump (Yes, External Tables).

1. Create a test user and grants:

sqlplus / as sysdba

create user test identified by test
default tablespace users temporary tablespace temp;

grant connect , resource to test;
grant create any directory to test;


2. Create a table and insert some records:

connect test/test

create table cur_objs(obj_id number, obj_name varchar2(128), cdate date, status varchar2(7));

insert into cur_objs
select object_id, object_name, created, status from all_objects;
commit;

insert into cur_objs
select * from cur_objs;
commit;

SQL> select count(1) from cur_objs;

COUNT(1)
----------
69020

SQL> SELECT count(1) FROM cur_objs
WHERE status = 'VALID'
AND obj_name like '%DBMS%';
2 3
COUNT(1)
----------
760


3. Create an Oracle directory and the data pump external table:

-- Check if the directory is valid:
$ cd /backup_ORADB/oracle/ORADB/data_ext
$ ls -lt
total 0
$

SQL> CREATE DIRECTORY data_ext_dir AS '/backup_ORADB/oracle/ORADB/data_ext';

Directory created.

SQL> DROP TABLE extract_cur_objs;


Table dropped.

SQL> CREATE TABLE extract_cur_objs
2 ORGANIZATION EXTERNAL
3 (TYPE ORACLE_DATAPUMP
4 DEFAULT DIRECTORY data_ext_dir
5 LOCATION ('extract_cur_objs1.exp', 'extract_cur_objs2.exp', 'extract_cur_objs3.exp')
6 )
7 PARALLEL 3 REJECT LIMIT 0
8 AS
9 SELECT obj_id, obj_name FROM cur_objs
10 WHERE status = 'VALID'
11 AND obj_name like '%DBMS%';

Table created.

SQL> !ls -lt [eE]*
-rw-r----- 1 oracle dba 24576 Dec 29 14:00 extract_cur_objs1.exp
-rw-r----- 1 oracle dba 12288 Dec 29 14:00 extract_cur_objs3.exp
-rw-r----- 1 oracle dba 16384 Dec 29 14:00 extract_cur_objs2.exp
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10019.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10021.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10017.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_9997.log

SQL>


4. Attaching the files:

DROP TABLE attach_files_cur_objs;

CREATE TABLE attach_files_cur_objs(
obj_id number,
obj_name varchar2(128))
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY data_ext_dir
LOCATION ('extract_cur_objs1.exp', 'extract_cur_objs2.exp', 'extract_cur_objs3.exp')
)
PARALLEL 3 REJECT LIMIT 0;


Side Note: impdp utility may not work with these files.