Thanx Edgar, Tht worked for me. Thanx a lot. On 1/23/06, Edgar Chupit <chupit@xxxxxxxxx> wrote: > > 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 > -- Thanks & Regards, T. Onkar Nath OneAPPS Enterprise Technology Pvt. Ltd. to_onkar@xxxxxxxxx onkarnath.tiwary@xxxxxxxxx