在 Oracle 中搜索对象（包括存储过程）的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/853547/
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
SQL to search objects, including stored procedures, in Oracle
提问by Glenn Wark
I need to write some sql that will allow me to query all objects in our Oracle database. Unfortunately the tools we are allowed to use don't have this built in. Basically, I need to search all tables, procedures, triggers, views, everything.
我需要编写一些 sql 来查询 Oracle 数据库中的所有对象。不幸的是，我们被允许使用的工具没有内置。基本上，我需要搜索所有表、过程、触发器、视图，一切。
I know how to search for object names. But I need to search for the contents of the object. i.e. SELECT * FROM DBA_OBJECTS WHERE object_name = '%search string%';
我知道如何搜索对象名称。但是我需要搜索对象的内容。即 SELECT * FROM DBA_OBJECTS WHERE object_name = '%search string%';
Thanks, Glenn
谢谢，格伦
采纳答案by Drevak
i'm not sure if i understand you, but to query the source code of your triggers, procedures, package and functions you can try with the "user_source" table.
我不确定我是否理解您，但是要查询您的触发器、过程、包和函数的源代码，您可以尝试使用“user_source”表。
select * from user_source
回答by Chris Cameron-Mills
I'm not sure I quite understand the question but if you want to search objects on the database for a particular search string try:
我不确定我是否完全理解这个问题，但如果您想在数据库中搜索特定搜索字符串的对象，请尝试：
SELECT owner, name, type, line, text
FROM dba_source
WHERE instr(UPPER(text), UPPER(:srch_str)) > 0;
From there if you need any more info you can just look up the object / line number.
从那里如果您需要更多信息，您可以查找对象/行号。
回答by Steve Ronaldson Ewing
I would use DBA_SOURCE (if you have access to it) because if the object you require is not owned by the schema under which you are logged in you will not see it.
我会使用 DBA_SOURCE（如果您有权访问它），因为如果您需要的对象不属于您登录的架构，您将看不到它。
If you need to know the functions and Procs inside the packages try something like this:
如果您需要了解包中的函数和 Procs，请尝试以下操作：
select * from all_source
where type = 'PACKAGE'
and (upper(text) like '%FUNCTION%' or upper(text) like '%PROCEDURE%')
and owner != 'SYS';
The last line prevents all the sys stuff (DBMS_ et al) from being returned. This will work in user_source if you just want your own schema stuff.
最后一行防止返回所有 sys 内容（DBMS_ 等）。如果您只想要自己的架构内容，这将在 user_source 中工作。
回答by Newton fan 01
i reached this question while trying to find all procedures which use a certain table
我在尝试查找使用某个表的所有程序时遇到了这个问题
Oracle SQL Developer offers this capability, as pointed out in this article : https://www.thatjeffsmith.com/archive/2012/09/search-and-browse-database-objects-with-oracle-sql-developer/
正如本文所指出的，Oracle SQL Developer 提供了此功能：https: //www.thatjeffsmith.com/archive/2012/09/search-and-browse-database-objects-with-oracle-sql-developer/
From the View menu, choose Find DB Object. Choose a DB connection. Enter the name of the table. At Object Types, keep only functions, procedures and packages. At Code section, check All source lines.
从视图菜单中，选择查找数据库对象。选择一个数据库连接。输入表的名称。在对象类型中，只保留函数、过程和包。在代码部分，选中所有源代码行。
回答by Sireesh Yarlagadda
ALL_SOURCE describes the text source of the stored objects accessible to the current user.
ALL_SOURCE 描述了当前用户可以访问的存储对象的文本源。
Here is one of the solution
这是解决方案之一
select * from ALL_SOURCE where text like '%some string%';
