获取 Oracle 数据库中所有函数和过程的列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1819447/
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
Get a list of all functions and procedures in an Oracle database
提问by AJM
I'm comparing three Oracle schemas.
我正在比较三个 Oracle 模式。
I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not)
我想获得每个数据库中使用的所有函数和过程的列表。这可以通过查询实现吗？（最好包括一个关于它们是否编译的标志）
Ideally it would be great to have a single query with a flag that states whether the function/procedure is in each schema. But even just the first bit would be better than manually inspecting each schema.
理想情况下，最好有一个带有标志的查询，该标志说明函数/过程是否在每个模式中。但即使只是第一位也比手动检查每个模式要好。
回答by Erich Kitzmueller
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE')
The column STATUS tells you whether the object is VALID or INVALID. If it is invalid, you have to try a recompile, ORACLE can't tell you if it will work before.
STATUS 列告诉您对象是 VALID 还是 INVALID。如果无效，则必须尝试重新编译，ORACLE 之前无法告诉您它是否可以工作。
回答by Philip Schlump
Do a describe on dba_arguments, dba_errors, dba_procedures, dba_objects, dba_source, dba_object_size. Each of these has part of the pictures for looking at the procedures and functions.
对 dba_arguments、dba_errors、dba_procedures、dba_objects、dba_source、dba_object_size 进行描述。其中每一个都有部分图片用于查看程序和功能。
Also the object_type in dba_objects for packages is 'PACKAGE' for the definition and 'PACKAGE BODY" for the body.
此外，包的 dba_objects 中的 object_type 是定义的“PACKAGE”和主体的“PACKAGE BODY”。
If you are comparing schemas on the same database then try:
如果要比较同一数据库上的模式，请尝试：
select * from dba_objects
where schema_name = 'ASCHEMA'
and object_type in ( 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'PACKAGE BODY' )
minus
select * from dba_objects
where schema_name = 'BSCHEMA'
and object_type in ( 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'PACKAGE BODY' )
and switch around the orders of ASCHEMA and BSCHEMA.
并切换 ASCHEMA 和 BSCHEMA 的顺序。
If you also need to look at triggers and comparing other stuff between the schemas you should take a look at the Article on Ask Tom about comparing schemas
如果您还需要查看触发器并比较模式之间的其他内容，您应该查看关于比较模式的 Ask Tom 文章
