G'day,
I'm trying to selectively import rows from a large number of tables (400+). Because
listing the tables in an "include" parameter would exceed the 4k limit by a
country mile, I resorted to using a sub-select instead. This is proven to work, in fact I
have done this with expdp on the same database where I encountered the following problem:
[07/15/16 08:44:40][oracle@tstsrv][S:TST][/home/oracle]
$ impdp system/password parfile=tmp2.par
Import: Release 11.2.0.4.0 - Production on Fri Jul 15 08:45:34 2016
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 and Automatic Storage Management options
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.KUPM$MCP", line 3215
The parameter file tmp2.par has the following content:
$ cat tmp2.par
schemas='MYSCHEMA'
query="where CREATED > sysdate - 21"
include=table:"in (select table_name from THATSCHEMA.refr$table@REFR$PRDCOPY where
table_owner = 'MYSCHEMA' and date_column_1 = 'UPDATED' and nvl(date_column_2, 'XXXX') =
'XXXX')"
table_exists_action=skip
network_link=REFR$PRDCOPY
parallel=6
nologfile=yes
The ORA-39071 seems spurious as the query is correct, as demonstrated in
sqlplus:
$ sp system/password
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 15 08:43:28 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
SQL> select table_name from THATSCHEMA.refr$TABLE@REFR$PRDCOPY where
table_owner = 'MYSCHEMA' and date_column_1 = 'CREATED' and nvl(date_column_2,
'XXXX') = 'XXXX';
TABLE_NAME
------------------------------
TABLE1
TABLE2
TABLE3
TABLE4
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
When I list the 4 tables returned in this query, instead of the subquery, the
import succeeds:
$ cat tmp.par
schemas='MYSCHEMA'
query="where CREATED > sysdate - 21"
include=table:"in ('TABLE1','TABLE2','TABLE3','TABLE4')"
table_exists_action=skip
network_link=REFR$PRDCOPY
parallel=6
nologfile=yes
$ impdp system/password parfile=tmp.par
Import: Release 11.2.0.4.0 - Production on Fri Jul 15 08:48:00 2016
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 and Automatic Storage Management options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** parfile=tmp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19.27 GB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "MYSCHEMA"."TABLE1" 0 rows
. . imported "MYSCHEMA"."TABLE2" 16282 rows
. . imported "MYSCHEMA"."TABLE4" 2728 rows
. . imported "MYSCHEMA"."TABLE3" 36643 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
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
...<foreign key definition>...
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Fri Jul 15
08:51:04 2016 elapsed 0 00:03:03
As stated earlier, subqueries do work when local tables are referenced. My gut
feeling is therefore that the database link in the subquery is the problem. I
tried with a db link owned by system (i.e. the connected user) and a public
database link. The link works, as demonstrated in sqlplus and with the staticly
defined tables import. I don't see how the db link in the subquery could
present a problem..?
Cheers,
Tony
--
//www.freelists.org/webpage/oracle-l
