4 Replies Latest reply: Jan 23, 2009 12:09 AM by 200754 RSS

    How to set ACTION of DB Link Session

    200754
      Hi all,

      How can I set ACTION of a session created by Oracle when I use a database link.

      Following is an example:
      -- In DB1
      SQL>CONN user1/hispassword@db1
      Connected.

      SQL> EXEC SYS.DBMS_APPLICATION_INFO.SET_ACTION('TEST ACTION');
      PL/SQL procedure successfully completed.

      SQL> SELECT ACTION FROM V$SESSION WHERE USERNAME='USER1';
      ACTION
      ---
      TEST ACTION

      SQL> SELECT SYSDATE FROM DUAL@DB2;
      SYSDATE
      ---
      23-JAN-09

      -- In DB2

      SQL> SELECT ACTION FROM V$SESSION WHERE USERNAME='USER1';
      ACTION
      ----   
        • 1. Re: How to set ACTION of DB Link Session
          JustinCave
          You would have to execute the DBMS_APPLICATION_INFO.SET_ACTION package on the remote database, i.e.
          SQL> EXEC dbms_application_info.set_action@db2( 'Some Action' );
          Of course, I am assuming from your queries that the database link DB2 is configured to connect to the remote database as user USER1 (i.e. that you either specified that the database link use the CURRENT USER or that you specified a hard-coded user name of USER1). If the database link is not connecting to DB2 as USER1, then the query on DB2 against V$SESSION would need to be modified to look at sessions for the particular user the database link is using.

          Justin
          • 2. Re: How to set ACTION of DB Link Session
            200754
            Hi Justin,

            I had specified a hard-coded user name of USER1 in DB2 database link.

            I tested your idea and below is what I got:
            SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION('TEST ACTION')@db2 ; 
            BEGIN DBMS_APPLICATION_INFO.SET_ACTION('TEST ACTION')@db2 ; END;
                                                                 *
            ERROR at line 1:
            ORA-06550: line 1, column 54:
            PLS-00103: Encountered the symbol "@" when expecting one of the following:
            := . ( % ;
            ORA-06550: line 1, column 71:
            PLS-00103: Encountered the symbol "END"
            If you have any other idea that enables me to distinguish the session in DB2, I would appreciate it.
            • 3. Re: How to set ACTION of DB Link Session
              JustinCave
              Note that the database link goes after the name of the procedure, as I specified above, not after the parameter list. So
              <<procedure name>>@<<database link>>( <<parameter list>> )
              not
              <<procedure name>>( <<parameter list>> )@<<database link>>
              Justin
              • 4. Re: How to set ACTION of DB Link Session
                200754
                Thanks a lot Justin. It works just fine.