Steven - thanks for pointing that out.
the user DEMO has the default tablespace TS_DATA and the exported objects were from USERS tablespace, upon import the user DEMO tried to create the objects in USERS tablespace and hence it failed since the user DEMO don't have any quota assigned on the USERS tablespace.
after introducing "REMAP_TABLESPACE" parameter, invoking DBMS_DATAPUMP produces the output like this.
rajesh@ORA11G> conn demo/demo@ora11g
Connected.
demo@ORA11G> declare
2 l_job int;
3 begin
4 l_job := dbms_datapump.open(
5 operation=>'IMPORT',
6 job_mode=>'SCHEMA',
7 job_name=>'DEMO_IMPORT');
8
9 dbms_datapump.add_file(
10 handle=>l_job,
11 filename=>'EXPDAT.DMP',
12 directory=>'TMP');
13
14 dbms_datapump.add_file(
15 handle=>l_job,
16 filename=>'implog2.txt',
17 directory=>'TMP',
18 filetype=>dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
19
20 dbms_datapump.metadata_remap(
21 handle=>l_job,
22 name=>'REMAP_SCHEMA',
23 old_value=>'SCOTT',
24 value=>'DEMO');
25
26 dbms_datapump.metadata_remap(
27 handle=>l_job,
28 name=>'REMAP_TABLESPACE',
29 old_value=>'USERS',
30 value=>'TS_DATA');
31
32 dbms_datapump.metadata_filter(
33 handle=>l_job,
34 name=> 'NAME_EXPR',
35 value=> 'NOT LIKE ''%$%''',
36 object_type=>'TABLE');
37
38 dbms_datapump.start_job(l_job);
39
40 dbms_datapump.detach(l_job);
41 end;
42 /
PL/SQL procedure successfully completed.
demo@ORA11G> $type d:\implog2.txt
Master table "DEMO"."DEMO_IMPORT" successfully loaded/unloaded
Starting "DEMO"."DEMO_IMPORT":
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "DEMO"."DEMO_IMPORT" successfully completed at Tue Mar 14 08:03:58 2017 elapsed 0 00:00:02
demo@ORA11G> select table_name from user_tables;
TABLE_NAME
------------------------------
BIG_TABLE
demo@ORA11G>
Tried to invoke IMPDP from command line got this.
D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>impdp demo/demo@LT035221/ORA11g directory=TMP dumpfile=EXPDAT.dmp remap_schema=scott:demo schemas=scott remap_tablespace=USERS:T
S_DATA logfile=implog.txt
Import: Release 11.2.0.4.0 - Production on Tue Mar 14 08:05:21 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEMO"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_SCHEMA_01": demo/********@LT035221/ORA11g directory=TMP dumpfile=EXPDAT.dmp remap_schema=scott:demo schemas=scott remap_tablespace=USERS:TS_DA
TA logfile=implog.txt
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."DEPT" 5.937 KB 4 rows
. . imported "DEMO"."EMP" 8.570 KB 14 rows
. . imported "DEMO"."EMP$" 8.570 KB 14 rows
. . imported "DEMO"."SALGRADE" 5.867 KB 5 rows
. . imported "DEMO"."BONUS" 0 KB 0 rows
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DEMO"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Mar 14 08:05:26 2017 elapsed 0 00:00:04
when included EXCLUDE parameter it went like this.
D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>impdp demo/demo@LT035221/ORA11g directory=TMP dumpfile=EXPDAT.dmp remap_schema=scott:demo schemas=scott remap_tablespace=USERS:T
S_DATA logfile=implog.txt EXCLUDE=TABLE:" like '%$%' "
Import: Release 11.2.0.4.0 - Production on Tue Mar 14 08:05:43 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDI-00014: invalid value for parameter, 'exclude'
D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>
Help me to understand these questions.
Questions:1) Why the DBMS_DATAMPUMP doesn't import objects, while IMPDP does it? did i missing something with DBMS_DATAMPUMP here ?
2) why the EXCLUDE parameter for IMPDP resulted in errors?
March 22, 2017 - 6:41 am UTC
Add a SCHEMA_EXPR as well
SQL> create user demo identified by demo;
User created.
SQL> grant connect, resource to demo;
Grant succeeded.
SQL> alter user demo quota unlimited on demo;
User altered.
SQL> alter user demo quota unlimited on users;
User altered.
SQL>
SQL> declare
2 l_job int;
3 begin
4 l_job := dbms_datapump.open(
5 operation=>'IMPORT',
6 job_mode=>'SCHEMA',
7 job_name=>'DEMO_IMPORT');
8
9 dbms_datapump.add_file(
10 handle=>l_job,
11 filename=>'scott.dmp',
12 directory=>'TEMP');
13
14 dbms_datapump.add_file(
15 handle=>l_job,
16 filename=>'implog'||to_char(sysdate,'HH24MISS')||'.txt',
17 directory=>'TEMP',
18 filetype=>dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
19
20 dbms_datapump.metadata_filter(
21 handle => l_job,
22 name => 'SCHEMA_EXPR',
23 value => '= ''SCOTT''');
24
25 dbms_datapump.metadata_remap(
26 handle=>l_job,
27 name=>'REMAP_SCHEMA',
28 old_value=>'SCOTT',
29 value=>'DEMO');
30
31 dbms_datapump.metadata_remap(
32 handle=>l_job,
33 name=>'REMAP_TABLESPACE',
34 old_value=>'USERS',
35 value=>'DEMO');
36
37 dbms_datapump.metadata_filter(
38 handle => l_job,
39 name => 'NAME_EXPR',
40 value => 'NOT LIKE ''%$%''',
41 object_type => 'TABLE');
42
43 dbms_datapump.start_job(l_job);
44
45 dbms_datapump.detach(l_job);
46 end;
47 /
PL/SQL procedure successfully completed.
SQL>
SQL> host cat c:\temp\imp143412.txt
Master table "MCDONAC"."DEMO_IMPORT" successfully loaded/unloaded
Starting "MCDONAC"."DEMO_IMPORT":
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"DEMO" already exists
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
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/STATISTICS/MARKER
Job "MCDONAC"."DEMO_IMPORT" completed with 1 error(s) at Wed Mar 22 14:34:12 2017 elapsed 0 00:00:03
SQL>
SQL> select segment_name, tablespace_name
2 from dba_segments
3 where owner = 'DEMO';
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------
EMP DEMO
DEPT DEMO
SALGRADE DEMO
EMP_PK DEMO
DEPT_PK DEMO