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 used a DB link to connect to another DB and delete a couple of records. Later I ran an Informatica Workflow (ETL tool) to load data into that database using a DB connection that connects directly to it.

The Workflow hasn't committed any records since 2hrs (it should run just for 30mins or so..) so I am guessing it is because I was not able to run "commit" using the DB link. The ETL tools logs don't provide any error or debug information at this point.

Any ideas how I could debug this..? I ran another ETL process to update a table and then run commit. But even after that the first process keeps on running without committing any records. I am not a DBA expert so probably am misinterpreting the DB behavior.. would really appreciate any suggestions. Thanks!

share|improve this question
    
Are you using an Oracle-to-Oracle database link? Or are you using Heterogeneous Services? If so, which gateway are you using? –  Justin Cave Feb 12 '13 at 18:18
    
@Justin, It's Oracle to Oracle. –  Kent Pawar Feb 12 '13 at 18:22

1 Answer 1

up vote 1 down vote accepted

Since you're using a standard Oracle-to-Oracle database link, Oracle automatically does two-phase commits. If you commit (or rollback) on one system, any changes made as part of the same transaction on the other system will committed (or rolled back) as well.

If there is an open transaction on the remote system that has row-level locks, that strongly implies that the local session is still there as well. So you probably just have to do a commit (or a rollback) in the local session to remove the locks.

If the ETL process is really stuck waiting on locks that are held by the other session, you should be able to query gv$session on the remote database and see that the session that the ETL application is running is waiting on row-level lock contention. Specifically, you would expect to see an event of enq: TX - row lock contention in gv$session. You would also expect that if you queried dba_blockers that you would see the session ID of the session that connected from the local database over the database link and that if you queried dba_waiters that you would see that the ETL process's session was blocked by that session.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.