inserts through a database link

69835 pts.
Tags:
Availability
Backup and Recovery
Database
IBM DB2
Installation
IT architecture
Microsoft Windows
Migration
Oracle
Oracle 8i
Oracle administration
Oracle Database
Oracle development
Performance/Tuning
Security
SQL
XML
Hello every body. Here I go again... Do you know if there is a restriction about performing inserts through a database link ? We are experiencing the following situation: We have two Oracle 8i instances running on a win 2k3 server, and a Forms applications needs to insert data from one instance to the second one. It executes an "insert - select", inserting data on a table which resides in instance b from a table which resides in instance a, but when the app try to execute the insert, it hangs, and the only way to stop it is killing the process. If the same application executes the "insert" (with fixed values) and the "select" separately, it has no problems. On instance "a" we have a public synonym for the instance "b" table. (create public synonym TABLE1 for TABLE1@DBLINK). We tried the same insert-select from Sql Plus, and it was done without any problem. We tried to remove the synonym with DBA Studio, but it was not possible. DBA Studio hanged too. Questions: - Would it be better to use <TABLE>@<DBLINK> in the application instead of creating the synonym ? - Is there some restriction about inserting through a dblink ? - Is it possible that we have this problem due to a "bad" Oracle (database) or Developer installation ? Any ideas will be really appreciated. Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

It has been a long time since I used Forms, but the situation sounds peculiar. I also haven’t worked on Oracle version 8 in a whaile.

The only thing I can imagine that would cause a hang – would be an insert in progress for the same primary key. I can duplicate the hung insert, but Oracle 9i let me drop the synonym while the two sessions were fighting over the insert. ;^> Perhaps that was different in version 8.

Sometiimes when a session gets killed, especially over a db link, the background operation can “live on”. It should time out after a while,or you could try bouncing the remote database.

I hope this helps.

-MrO-

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    I haven't tried to use a synonym. I use the table@dbLink method. Have you tried that? If that still doesn't work, I have used the INSERT INTO SELECT FROM statement in Forms with the dbms_sql procedure. I needed to do this because I needed to dynamically create the sql statement because I could have variable database links; but it will work for just one database link as well. I would say that this would make it work like SQL*Plus. DECLARE cursor ccomp is select prodgroup from eisuser.company@wpc; source_cursor integer; rows_processed integer; sqlstmt varchar2(1000); BEGIN set_application_property(CURSOR_STYLE,'BUSY'); commit; for pg IN ccomp loop sqlstmt := 'delete from owner.table@' || pg.prodgroup || ' where customer = ''' || :customer || ''''; -- message(sqlstmt); source_cursor := dbms_sql.open_cursor; dbms_sql.parse(source_cursor, sqlstmt, 1); rows_processed := dbms_sql.execute(source_cursor); -- message('Rows deleted (' || pg.prodgroup || '): ' || TO_CHAR(rows_processed)); sqlstmt := 'insert into owner.table@' || pg.prodgroup || ' select * from owner.table where customer = ''' || :customer || ''''; -- message(sqlstmt); source_cursor := dbms_sql.open_cursor; dbms_sql.parse(source_cursor, sqlstmt, 1); rows_processed := dbms_sql.execute(source_cursor); -- message('Rows added (' || pg.prodgroup || '): ' || TO_CHAR(rows_processed)); end loop; commit; set_application_property(CURSOR_STYLE,'DEFAULT'); END;
    1,740 pointsBadges:
    report
  • carlosdl
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/inserts-through-a-database-link/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following