Re: dblink in PL/SQL block..

From: Edgar Chupit <chupit_at_gmail.com>
Date: Mon, 23 Jan 2006 09:57:55 +0200
Message-ID: <a8f0771c0601222357o7cbc2a01i355fa39e9859e226@mail.gmail.com>

Dear Onkar,

First of all to query dynamic performance views from remote destination, you should grant your dblink user access to underlying view/fixed table. In your case you should grant select on v_$session and v_$sqltext_with_newlines to your dblink user. For example, suppose that I have dblink trex_tt that will connect using user zulu credentials:

SQL> grant select on v_$session to zulu; Grant succeeded

SQL> grant select on v_$sqltext_with_newlines to zulu; Grant succeeded

After that you can run your queries to
v$session/v$sqltext_with_newlines to query from dblink.

Than after small changed in your code, it compiles and executes just fine (I've changed line 14 and 25):

SQL> create or replace procedure showsql as   2 -- x number;
  3 cursor c1 is
  4 select username||'('||sid||','||serial#||

  5                  ') ospid = ' ||  process ||
  6                  ' program = ' || program username,
  7               to_char(LOGON_TIME,' Day HH24:MI') logon_time,
  8               to_char(sysdate,' Day HH24:MI') current_time,
  9               sql_address, LAST_CALL_ET
10          from v$session_at_trex_tt
11         where status = 'ACTIVE'
12           and rawtohex(sql_address) <> '00'
13           and username is not null order by last_call_et;
14      cursor c2(x c1%rowtype) is
15   select max(decode(piece,0,sql_text,null)) ||
16                            max(decode(piece,1,sql_text,null)) ||
17                            max(decode(piece,2,sql_text,null)) ||
18                            max(decode(piece,3,sql_text,null))
19                                 sql_text
20                       from v$sqltext_with_newlines_at_trex_tt
21                      where address = x.sql_address
22                        and piece < 4;
23  begin
24      for x in c1 loop
25          for y in c2(x)  loop
26              if ( y.sql_text not like '%listener.get_cmd%' and
27                   y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
28              then
29                  dbms_output.put_line( '--------------------' );
30                  dbms_output.put_line( x.username );
31                  dbms_output.put_line( x.logon_time || ' ' ||
32                                        x.current_time||
33                                        ' last et = ' ||
34                                        x.LAST_CALL_ET);
35                  dbms_output.put_line(
36                            substr( y.sql_text, 1, 250 ) );
37              end if;
38          end loop;
39      end loop;

40 end;
41
42
43 /

Procedure created.

SQL> sho err
No errors.

On 1/23/06, Onkar N Tiwary <onkarnath.tiwary_at_gmail.com> wrote:
> hi all,
>
> I have one small problem. Below is my query which is running on the sql
> prompt:
>
>
> Any idea???????
>
> --
> Thanks & Regards,
> T. Onkar Nath
> OneAPPS Enterprise Technology Pvt. Ltd.
> to_onkar_at_yahoo.com
> onkarnath.tiwary_at_gmail.com
>

--
Best regards,
  Edgar Chupit
  callto://edgar.chupit
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 23 2006 - 01:57:55 CST