See Outlines
Only once:
As SYS:
grant all on v_$sqltext to aldo;
As ALDO:
create or replace function getSql(mhash_value number) return clob is
cursor c is
select sql_text from V$SQLTEXT
where hash_value = mhash_value
order by piece;
rc varchar2(64);
sqltext_clob clob;
begin
dbms_lob.createtemporary(sqltext_clob,true);
open c;
loop
fetch c into rc;
exit when c%notfound;
dbms_lob.writeappend(sqltext_clob,length(rc),rc);
end loop;
close c;
return sqltext_clob;
end;
|
select u.username, s.*, getSql(s.hash_value) from v$sqlarea s, dba_users u where s.parsing_schema_id = u.user_id order by elapsed_time desc;
Calculate hash value, DIFFERENT from v$sql
Available in 10g only
select ora_hash(
'SELECT ORA_TQ_BASE$.NEXTVAL FROM DUAL', 4294967295, 0) h from dual
select DBMS_UTILITY.GET_HASH_VALUE (
'SELECT ORA_TQ_BASE$.NEXTVAL FROM DUAL',
0, POWER(2,16)-1) h from dual
Calculate hash value, SAME as v$sql, this is the only way
DECLARE
c number;
v_sqltxt varchar2(1000) := 'select ''test string'' from dual';
hv v$sql.hash_value%type;
BEGIN
dvms_output.enable(1000000);
c := dbms_sql.open_cursor
dbms_sql.parse(c, v_sqltxt, dbms_sql.native);
dbms_sql.close_cursor(c);
select hash_value
into hv
where sql_text = v_sqltxt;
dbms_output.put_line('Hash value ='||hv);
END;
/
|