Database links hangs

Does anyone have a clue what is wrong ?

We have a schema M and a schema owner M. From schema M we need a database link L to another database (and a schema A). This database link must be created and droped by the user U dynamically (in a .pc-file ) , as the schema A can be on any of a small number of machines. We use Oracle NT 7.3.4.x on the serverside.

We use the database link in PL/SQL procedure packages (static PL/SQL), owned by M. As we cannot use DDL in PL/SQL the name of the database link must be the same for all users U1, U2..., that happends to be running. No database link is public, each has it's owner (Ux) so that should not be a problem. User U is granted execute of all packages created by M, via a role R. The link L itself I for simplicity let to be connected A as the schema owner A.

I can with no problem connect as user U in SQL*Plus against the schema M (synonyms), set the role R (grants of M:s packages) and then use my link L as the implicit user A to access tables directly in schema A. However, the problem occurs when the user U creates the database link L and then access the link from inside the procedure package. I do the same simple select like in PL*SQL. The select hangs and finally after several minutes it's timed out with a 'no rows found' (no SQL -error). However, interestingly, if the schema owner M previously has created a link with the same name, user U use that link in the package with no hang, but note, not the link he created himself (same name).

TIA Johan Thorselius

Received on Thu Jun 17 1999 - 14:04:47 CDT