ERROR: ORA-02019: connection description for remote database not found

[balajiyes]

Hi All,

Please help on the below error.

ERROR:
ORA-02019: connection description for remote database not found
ORA-02063: preceding line from EDWTX03@TRAFFIC_OWNER

I am getting the above error when I try to run the below query.

The query works fine when I remove the seq_tap_td_imsi_id.NEXTVAL. The owner of this sequence and package are same. They exist on the same schema.

In the FROM clause, I have a local table (calendar_time) and a remote table (TAP).

problem query from package:
-------------------------------
SELECT seq_tap_td_imsi_id.NEXTVAL, -- sequence in local database/schema
....
FROM (
SELECT /*+ DRIVING_SITE (T) NO_MERGE FULL(t) FULL(ct) */
....
FROM
tap --remote_table accessed by dblink -> synonym remote database
, calendar_time ct -- table local database/schema
WHERE t.source_system_id = '016'
....
GROUP BY to_number(to_char(t.charge_start_date, 'YYYYMMDD')),
....

Please.

Let me know if you need more information.


Thanks in Advance.

[PAVB]

Check DBLink
Check TNSNAMES entry referenced by DBLink
tnsping TNSNAMES entry referenced by DBLink

[LKBrwn_DBA]

....
FROM (
SELECT /*+ DRIVING_SITE (T) NO_MERGE FULL(t) FULL(ct) */
....
FROM
tap --remote_table accessed by dblink -> synonym remote database
, calendar_time ct -- table local database/schema
WHERE t.source_system_id = '016'
....

After you check what Pablo recommends and you still have an issue, change the "tap" from synonym to a view:

CREATE OR REPLACE VIEW tap
AS
   SELECT *
     FROM remote_table@dblink;

[balajiyes]

Thanks guys.

I checked the TNSPING. I am able to get a positive response from both the databases.
I cannot create a view because, this code is working in one environment. The code in our production environment works, but we are getting an error when we are running the jobs in our new testing environment.

Not sure if we need to check any specific parameters in the databases.

Any info on that.

Thanks again

[LKBrwn_DBA]

Check:

init.ora (db_domain vs global_names={true|false}) vs sqlnet.ora (NAMES.DEFAULT_DOMAIN) vs tnsnames.ora(database alias and service name)