Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a schema with some database links to other schemas that we use to pull data from. We pull the data into a staging table, massage it, and then do some merges into our schema/database.

The data in the linked schema is a little much to pull into test systems, so I want to only get the first 10000 records, which is easy enough.

select * from 
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

This returns very quickly as in under 2 seconds.

However when I try to insert this into my staging table, it causes SQL Developer to hang. Not a windows hang, the Script Output window just shows "ScriptRunner Task" with the moving bar back and forth showing that it's doing something.

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
select * from 
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

When it does this I can see that there are DW locks, but nothing else is using the linked databases that I'm trying to query.

Any help would be greatly appreciated as I am very much a novice with Oracle.

share|improve this question
    
Could you check the alert.log for any issue? Also could you query v$lock and v$session_wait in join with v$process and v$session to check where query is waiting? –  Ste Jun 6 '13 at 7:17
    
Have a look at the execution plans for differences, in particular for signs that the complete data sets for the two tables are being read over the db link and the join executed locally. –  David Aldridge Jun 6 '13 at 7:55
    
I would create a view encapsulating your query on the origin database and then query the view. That way you force the join and trimming to be done on the origin database –  kevinsky Jun 6 '13 at 13:34
    
@Ste - There wasn't anything in the alert.log for my ip address. There are lots of "Fatal NI connect error 12170." however. I'm digging into the other thing you suggested. –  sdanna Jun 6 '13 at 20:04
    
@Ste - This is the query I'm using. I'm not sure what to make of the data that comes back though in regards to checking where the query is waiting. Select * From V$lock L Join V$session_Wait Sw On L.Sid = Sw.Sid Join V$session S On L.Sid = S.Sid join v$process p on s.paddr = p.addr where block = 1; –  sdanna Jun 6 '13 at 20:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.