Does altering a session invalidate Pakage States in Oracle 10g?

Question

Hi,

I have a package that gets invalidated on a regular basis and found this in the code:

ALTER SESSION CLOSE  DATABASE LINK;

Can this invalidate package states? Though I can't seem to replicate it.

create or replace package body invalid_package_state_test is

procedure test is
 TEMP VARCHAR2(1) := NULL;

begin
 SELECT 'Y' INTO TEMP FROM dual@dw;
DBMS_OUTPUT.PUT_LINE('Testing');


     EXECUTE IMMEDIATE
   'ALTER SESSION CLOSE  DATABASE LINK DW';
   EXCEPTION WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE('DBLink Not Open');
end test;
end invalid_package_state_test;

Also, can someone explain when does one need the ALTER SESSION CLOSE DATABASE LINK;.

Thoughts?

Thanks! Jonas

Answer 1

Q1. Does closing a database link invalidate the package?

Not sure, but if so I suspect it'll be because of the "SELECT FROM dual@dw", not because of the ALTER. What happens if you hide dual@dw behind a local view? Does the db link get created/dropped from time to time or is it created just once?

Q2. Why close a database link?

From the documentation:

Closing Database Links

If you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link. This situation has the following consequences:

If 20 users open sessions and access the same public link in a local database, then 20 database link connections are open.

If 20 users open sessions and each user accesses a private link, then 20 database link connections are open.

If one user starts a session and accesses 20 different links, then 20 database link connections are open.

After you close a session, the links that were active in the session are automatically closed. You may have occasion to close the link manually. For example, close links when:

The network connection established by a link is used infrequently in an application.

The user session must be terminated.

If you want to close a link, issue the following statement, where linkname refers to the name of the link:

ALTER SESSION CLOSE DATABASE LINK linkname;

Note that this statement only closes the links that are active in your current session.

Source: Oracle 10gR2 docs