Re: dblink in PL/SQL block..
- From: Edgar Chupit <chupit@xxxxxxxxx>
- To: onkarnath.tiwary@xxxxxxxxx
- Date: Mon, 23 Jan 2006 09:57:55 +0200
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@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@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@xxxxxxxxx> 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@xxxxxxxxx
> onkarnath.tiwary@xxxxxxxxx
>
--
Best regards,
Edgar Chupit
callto://edgar.chupit
--
//www.freelists.org/webpage/oracle-l
Other related posts:
