Oracle：搜索所有存储的过程/触发器/其他数据库代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5735463/
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
Oracle: search though all stored procs/triggers/other db code?
提问by chris
Is it possible to search through all of the programmatic objects (functions, stored procedures, triggers, etc) across multiple schemas in Oracle?
是否可以在 Oracle 中跨多个模式搜索所有编程对象（函数、存储过程、触发器等）？
There are a number of tables that appear to be unused in my code, but I don't want to break anything by removing them without checking first.
我的代码中有许多表似乎未使用，但我不想通过删除它们而不先检查来破坏任何内容。
回答by Justin Cave
It is possible to search through object code-- you'd generally use the DBMS_METADATA package to generate the DDL for the object and then search the CLOB. However, it doesn't sound like that's actually what you want to do.
可以搜索对象代码——您通常会使用 DBMS_METADATA 包为对象生成 DDL，然后搜索 CLOB。然而，这听起来并不是你真正想要做的。
If you are just trying to figure out whether a table is referenced by any code in your system, you would generally want to use the DBA_DEPENDENCIESview (or ALL_DEPENDENCIESor USER_DEPENDENCIESdepending on your privileges and the scope of what you're looking for). Something like
如果您只是想弄清楚系统中是否有任何代码引用了一个表，您通常会希望使用该DBA_DEPENDENCIES视图（ALL_DEPENDENCIES或USER_DEPENDENCIES取决于您的权限和您要查找的范围）。就像是
SELECT *
FROM dba_dependencies
WHERE referenced_owner = 'SCOTT'
AND referenced_name = 'EMP'
AND referenced_type = 'TABLE'
will show you everything that depends on the EMPtable in the SCOTTschema.
将向您显示依赖于架构EMP中表的所有内容SCOTT。
The only time you'd want to search code rather than looking at DBA_DEPENDENCIESwould be when you had code that was doing dynamic SQL where the table name was hard-coded. But that's relatively unlikely in practice.
您想要搜索代码而不是查看代码的唯一时间DBA_DEPENDENCIES是当您有执行动态 SQL 的代码时，其中表名是硬编码的。但这在实践中相对不太可能。
回答by DCookie
You can search the DBA_SOURCE view:
您可以搜索 DBA_SOURCE 视图：
SELECT *
FROM dba_source
WHERE UPPER(text) LIKE '%YOUR_TABLE_NAME%';
回答by Chris
Do this in Toad by selecting:
通过选择在 Toad 中执行此操作：
Search => Object Search
搜索 => 对象搜索
