oracle 如何找出存储过程中使用了哪些表/视图/同义词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3090641/
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 to find out what tables/views/synonyms are used in a stored procedure
提问by jason
Through Oracle queries is it possible to find out which views/synonyms/tables a stored procedure use?
通过 Oracle 查询是否可以找出存储过程使用哪些视图/同义词/表？
In PL/SQL Developer if you collapse out a stored procedure it will show "References" which shows all the tables/views/synonyms that the stored procedure uses.
在 PL/SQL Developer 中，如果您折叠存储过程，它将显示“参考”，其中显示了存储过程使用的所有表/视图/同义词。
I am trying to implement this functionality into a script which will come in handy.
我正在尝试将此功能实现到一个会派上用场的脚本中。
Wondering if anyone knows a script that will fetch me all the synonyms/views/tables that a stored procedure uses?
想知道是否有人知道一个脚本可以获取存储过程使用的所有同义词/视图/表？
回答by Rene
The information you are looking for is in the user_dependencies/all_dependencies view.
您要查找的信息位于 user_dependencies/all_dependencies 视图中。
回答by Matas Vaitkevicius
The answer by @Rene is correct however I believe it needs additional explanation. When selecting from all_dependenciesyou might run query like one below that should give you all the objects that are referencing your SP.
@Rene 的答案是正确的，但我认为它需要额外的解释。当从中选择时，all_dependencies您可能会运行如下查询，它应该为您提供所有引用您的 SP 的对象。
SELECT *
FROM all_dependencies
WHERE "REFERENCED_NAME" = 'vcustomeraddresses';
You might be surprised when it will come back empty-handed.
This is because oracle is CASE SENSITIVE. What this means is that you have to ether disable case sensitivity(if version of oracle you are using is above 10g r2)
当它空手而归时，您可能会感到惊讶。
这是因为 oracle 区分大小写。这意味着您必须禁用区分大小写（如果您使用的 oracle 版本高于 10g r2）
ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;
or upperboth sides when comparing
或upper双方比较时
SELECT *
FROM all_dependencies
WHERE upper("REFERENCED_NAME") = upper('vcustomeraddresses');
Hope this saves you some time and frustration.
希望这可以为您节省一些时间和挫折。
