Sessions window

[rbrooker]

the session window is very useful for determining what sql is being run on the database. however, it is not the easiest to get that sql into a readable form. is there any chance that you could take the contents of v_$sqltext_with_newlines table which the session window uses, concatenate all the pieces together and run it through the beautifier so as to get something that is readable? it would be good to be able to copy it to a sql window as well.

cheers.

------------------
richard.brooker@vuw.ac.nz

 

[Marco Kalter]

You could create the following function under the SYS account:

create or replace function sqltext_full(p_address    in raw,
                                        p_hash_value in number) return varchar2 is
  result varchar2(4000);
begin
  result := '';
  for c in (select sql_text
              from v$sqltext_with_newlines
             where address = p_address
               and hash_value = p_hash_value
             order by piece)
  loop
    result := result | | c.sql_text;
  end loop;
  return(result);
end sqltext_full;

Grant it to public and create a public synonym:

SQL> grant execute on sqltext_full to public;
SQL> create public synonym sqltext_full for sqltext_full;

Now you can add a session query like this in the Sessions tool:

select sqltext_full(:sql_address, :sql_hash_value) full_text from dual

The result will be displayed as one line in the Sessions tool initially, but you can resize the row of the grid to show more lines, and you can always copy/paste it correctly.

------------------
Marco Kalter
Allround Automations