ops$tkyte%ORA11GR2> create or replace directory TKDIR as '/tmp'
2 /
Directory created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 l_handle number;
3 l_status Varchar2(200);
4 begin
5 l_handle := dbms_datapump.open(operation=>'EXPORT',
6 job_mode=>'SCHEMA',
7 job_name=>'MY_JOB');
8
9 dbms_datapump.add_file(handle=>l_handle,
10 filename=>'data1.dmp',
11 directory=>'TKDIR');
12
13 dbms_datapump.add_file(handle=>l_handle,
14 filename=>'log1.txt',
15 directory=>'TKDIR',
16 filetype=>dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
17
18 dbms_datapump.metadata_filter(handle=>l_handle,
19 name=>'SCHEMA_LIST',
20 value=>q'|'SCOTT','HR'|');
21
22 dbms_datapump.metadata_filter(handle=>l_handle,
23 name=>'NAME_EXPR',
24 value=>q'|in ('EMP','EMPLOYEES') |',
25 object_path=>'TABLE');
26
27 dbms_datapump.start_job(handle=>l_handle);
28 dbms_datapump.wait_for_job(handle=>l_handle,
29 job_state=>l_status);
30
31 dbms_output.put_line ('status = '||l_status);
32
33 exception
34 when others then
35 dbms_output.put_line ( dbms_utility.format_error_backtrace );
36 dbms_datapump.stop_job(l_handle);
37 raise_application_error (-20458,sqlerrm);
38 end;
39 /
status = COMPLETED
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> !cat /tmp/log1.txt
Starting "OPS$TKYTE"."MY_JOB":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 16.81 KB 107 rows
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
Master table "OPS$TKYTE"."MY_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$TKYTE.MY_JOB is:
/tmp/data1.dmp
Job "OPS$TKYTE"."MY_JOB" successfully completed at 11:57:32
see line 23, you materially changed my example. A list would not be "sql", I used a name_expr