Select Statment Using Dblink Works Fine, Insert Into Using Same Select Hangs (Doc ID 732999.1)

Last updated on SEPTEMBER 05, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.


Symptoms

-- Problem Statement:
Select statement using DBlinks works fine, but issuing insert into a table using the same select statement  hangs.

SQL> select distinct parloc.country gto_loc,
par.organization_id gto_org_id,
par.organization gto_org_name,
par.org_manager_number gto_mgr_num,
par.org_manager_name gto_mgr_name,
par.hr_manager_number gto_hrm_num,
par.hr_manager_name gto_hrm_name,
'',
loc.country org_loc,
org.organization_id org_id,
org.organization org_name,
org.org_manager_number org_mgr_num,
org.org_manager_name org_mgr_name,
org.hr_manager_number org_hrm_num,
org.hr_manager_name org_hrm_name,
'',
'',
'',
'999999999',
Sysdate
from crdohr.crdohr_person@ORDWHR10 per,
crdohr.crdohr_organization@ORDWHR10 org,
crdohr.crdohr_organization@ORDWHR10 par,
crdohr.crdohr_location@ORDWHR10 loc,
crdohr.crdohr_location@ORDWHR10 parloc
where per.curr_sub_business_group = 'GE Global Research'
and per.termination_date is null
and per.curr_band <> 'PEN'
and per.curr_org_id = org.organization_id
and org.location_id = loc.location_id
and org.PARENT_ORG_ID = par.organization_id
and par.location_id = parloc.location_id
and org.org_manager_number = '302000930'

Returns 1 row, however :

SQL> insert into testnow
select distinct parloc.country gto_loc,
par.organization_id gto_org_id,
par.organization gto_org_name,
par.org_manager_number gto_mgr_num,
par.org_manager_name gto_mgr_name,
par.hr_manager_number gto_hrm_num,
par.hr_manager_name gto_hrm_name,
'',
loc.country org_loc,
org.organization_id org_id,
org.organization org_name,
org.org_manager_number org_mgr_num,
org.org_manager_name org_mgr_name,
org.hr_manager_number org_hrm_num,
org.hr_manager_name org_hrm_name,
'',
'',
'',
'999999999',
Sysdate
from crdohr.crdohr_person@ORDWHR10 per,
crdohr.crdohr_organization@ORDWHR10 org,
crdohr.crdohr_organization@ORDWHR10 par,
crdohr.crdohr_location@ORDWHR10 loc,
crdohr.crdohr_location@ORDWHR10 parloc
where per.curr_sub_business_group = 'GE Global Research'
and per.termination_date is null
and per.curr_band <> 'PEN'
and per.curr_org_id = org.organization_id
and org.location_id = loc.location_id
and org.PARENT_ORG_ID = par.organization_id
and par.location_id = parloc.location_id
and org.org_manager_number = '302000930' ;

Hangs and has to be cancelled.




Cause

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