如何判断是否正在使用 PL/SQL 包、过程或函数?

问题描述

如何判断是否正在使用 PL/SQL 包、过程或函数?是否有包含有关 PL/SQL 包、过程或函数使用情况的统计信息的 Oracle 表或视图?

How can you tell if a PL/SQL Package, Procedure, or Function is being used? Is there an Oracle table or view that contains statistics on PL/SQL Package, Procedure, or Function usage?

推荐答案

你也可以试试查询USER/ALL_source:

You can also try querying USER/ALL_source:

SELECT * FROM all_source
where UPPER(TEXT) like UPPER('%procedure_name%')

或

SELECT * FROM all_source
where UPPER(TEXT) like UPPER('%package.function_name%')

您必须忽略自我引用，但这应该很容易发现.

You'll have to ignore self references, but that should be easy to spot.

您还需要检查来自 user/all_views 的查看"源.请参阅有关查询视图源的另一个问题.

You'll also need to check "view" source from user/all_views. See the other question about querying view source though.

您还可以检查包或顶级函数/过程是否与

you can also check if a package or top level function/procedure is used with

select * from all_dependencies
where referenced_name like '%PACKAGE_NAME%';

注意:根据需要将 user_ 切换为 all_/dba_

NB: switch user_ with all_/dba_ as needed

如果您专门寻找未调用的函数，那么另一种选择是在打开 WARNINGS 的情况下编译您的代码，然后寻找 PLW-06002 和 LPW-06006

if you are specifically looking for uncalled functions then another option is to compiler your code with WARNINGS turned on and then look for PLW-06002 and LPW-06006

exec DBMS_WARNING.add_warning_setting_cat('ALL','ENABLE','SESSION')
create or replace function x return number
as
procedure y is begin null; end;
begin
return 0;
return 1;
end;

show errors

Errors for FUNCTION X:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit X omitted optional AUTHID clause; default value DEFINER used
3/1      PLW-06006: uncalled procedure "Y" is removed.
6/1      PLW-06002: Unreachable code

这篇关于如何判断是否正在使用 PL/SQL 包、过程或函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！