ORA-00918 at EXPDP Level (Doc ID 563002.1)

Last updated on DECEMBER 23, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Goal

     The question is why it is not possible to prefix the column names in query at expdp level.
Expdp doesn't prefix the columns with ku$ in the select used in query parameter and
expdp process fails with ORA-918: column ambiguously defined. This issue occurs when
the columns with same names are existing in more than one table mentioned in query.
The issue can be reproduced with two tables, as follows:

create table tab_a
( id number,
text_a varchar2(10));

create table tab_b
( id number,
text_b varchar2(10));

insert into tab_a values (1, 'Text i');
insert into tab_a values (2, 'Text 2');
insert into tab_b values (1, 'Text i');
commit;

The statement:

select id, text_a
from tab_a ku$
inner join tab_b b on b.id = ku$.id;

or the similar one:

select id, text_a
from tab_a ku$, tab_b b
where b.id = ku$.id;

will fail with ORA-918 (column ID is existing in both joined tables).

The DataPump export job with:

#> expdp user/passw directory=dpu dumpfile=test.dmp tables=tab_a query=tab_a:"inner join tab_b b on b.id = ku$.id"

will fail with ORA-918, too.

In sqlplus we can prefix the column names in order to avoid the ORA-918 error, but this it is not possible
to be implemented at expdp level.

So, the issue can be summarized as follows:
- DataPump export is using the alias KU$ to identify the pivot table when QUERY is used
- Is it possible to qualify the column names in table with:

SELECT KU$.ID, KU$.TEXT_A --> instead of: SELECT ID, TEXT_A
FROM RELATIONAL (TAB_A) KU$
INNER JOIN TAB_B B ON B.ID = KU$.ID;

when the internal QUERY statement is generated in order to avoid the error ORA-918?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms