oracle 如何判断是否正在使用 PL/SQL 包、过程或函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4670164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can you tell if a PL/SQL Package, Procedure, or Function is being used?
提问by plsql_4_life
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?
如何判断是否正在使用 PL/SQL 包、过程或函数？是否有包含有关 PL/SQL 包、过程或函数使用情况的统计信息的 Oracle 表或视图？
回答by ShoeLace
You can also try querying USER/ALL_source:
您也可以尝试查询 USER/ALL_source：
SELECT * FROM all_source
where UPPER(TEXT) like UPPER('%procedure_name%')
or
或者
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.
您必须忽略自我引用，但这应该很容易发现。
You'll also need to check "view" source from user/all_views. See the other question about querying view source though.
您还需要从 user/all_views 检查“查看”源。请参阅有关查询视图源的另一个问题。
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%';
NB: switch user_ with all_/dba_ as needed
注意：根据需要使用 all_/dba_ 切换 user_
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
如果您专门寻找未调用的函数，那么另一种选择是在打开 WARNINGS 的情况下编译您的代码，然后查找 PLW-06002 和 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
回答by Tim
回答by René Nyffenegger
If you're on Oracle 11 (R2?), I'd give PL/Scopea chance.
如果您使用的是 Oracle 11 (R2?)，我会给您PL/Scope一个机会。
The docu states: PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage in the source code.
该文档指出：PL/Scope 是一个编译器驱动的工具，它在程序单元编译时收集有关 PL/SQL 源代码中标识符的数据，并使其在静态数据字典视图中可用。收集的数据包括有关标识符类型、用法（声明、定义、引用、调用、赋值）以及每个用法在源代码中的位置的信息。
PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.
PL/Scope 支持开发强大而有效的 PL/Scope 源代码浏览器，通过最大限度地减少浏览和理解源代码所花费的时间来提高 PL/SQL 开发人员的工作效率。
You can find more about it at http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#g1010526
您可以在http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#g1010526 上找到更多相关信息
回答by JordanBean
You can see if an object has any dependencies by querying the DBA_DEPENDENCIES table.
您可以通过查询 DBA_DEPENDENCIES 表来查看对象是否具有任何依赖关系。
SELECT OWNER,
NAME,
TYPE
FROM SYS.DBA_DEPENDENCIES
WHERE REFERENCED_OWNER = '<your object owner>'
AND REFERENCED_NAME = '<your object name>'
AND REFERENCED_TYPE IN ('PACKAGE', 'PROCEDURE', 'FUNCTION');
This query will return any dependencies in code stored inside the Oracle instance itself.
此查询将返回存储在 Oracle 实例本身内的代码中的任何依赖项。
It will not reveal whether or not any object is called outside of the instance.
它不会显示是否在实例之外调用了任何对象。
回答by Abhishek Kapoor
You can use Editors like Toad. They will directly list both the objects on which your procedure is dependent and objects which reference your procedure.
您可以使用 Toad 等编辑器。它们将直接列出您的过程所依赖的对象和引用您的过程的对象。
