My Oracle Support Banner

DatatPump Import Raises The Error ORA-980 Synonym Translation Is No Longer Valid (Doc ID 1448978.1)

Last updated on MARCH 12, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

After an import process DBA_SYNONYMS.TABLE_OWNER contains the schema name.

connect / as sysdba

set echo on feedback on

create user test1 identified by  test1;

grant dba to test1;

create user test2 identified by  test2;

grant dba to test2;

create table test1.table1 (c1 char(10) not null);

insert into test1.table1 values ('testing');

commit;

create public database link link1@loopback connect to test1 identified by test1 using 'ORCL1';

connect test2/test2

create synonym table1_syn for table1@link1@loopback;

select * from table1_syn;

C1

----------

testing

 

set null NULL

select * from dba_synonyms where db_link is not null;

OWNER  SYNONYM_NAME  TABLE_OWNER TABLE_NAME      DB_LINK

------ ------------- ----------- --------------- ---------------

TEST2  TABLE1_SYN    NULL        TABLE1          LINK1@LOOPBACK  

 **  The Table Owner is null. (expected)

 

expdp system/oracle dumpfile=symtest2.dmp schemas=test2 reuse_dumpfiles=yes

 

Database B:

===========

If we run an import, here is the results:

impdp system/oracle dumpfile=symtest2.dmp   schemas=test2

sqlplus test2/test2

set null NULL

select * from dba_synonyms where db_link is not null;

OWNER  SYNONYM_NAME  TABLE_OWNER TABLE_NAME      DB_LINK

------ ------------- ----------- --------------- ---------------

TEST2  TABLE1_SYN    TEST2       TABLE1          LINK1@LOOPBACK

 

After do an import DBA_SYNONYMS.TABLE_OWNER contains the schema name.

(incorrect result)

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.