1,查询哪个存储过程或者函数包含某字符串:
select* from all_SOURCEwhere/*type = 'FUNCTION'
and*/upper(text)like'%DW%SHOPPER%';
2,查询存储过程或者函数用到了哪些表?
select* from user_dependencieswhere name= 'F_SHPR_ONEDY_F'
select * from all_dependencies s,all_dependencies fwhere
s.REFERENCED_NAME=f.name and f.TYPE='FUNCTION'
and s.name='P_SHPR_ORDFRM_CTRL'
and f.REFERENCED_TYPEin('TABLE','SYNONYM','FUNCTION')
3,查询存储过程调用历史/sql语句执行历史性能
select t.sql_id,
t.sql_text,
s.plan_hash_value,
s.optimizer_cost,
s.executions_total,
s.elapsed_time_total,
s.disk_reads_total,
s.buffer_gets_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.sql_id=t.sql_id
and upper(t.sql_text)like'%P_SHPR_ORDFRM_CTRL%';