如何在 Oracle 10g 中获取无效对象的错误列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28710469/
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 fetch the list of errors for invalid objects in Oracle 10g
提问by User M
Explanation: I have more than 200 invalid objects in my DB, the reasons could be couple of objects only (others due to dependancy). Is there a way we can select the object name and the 'Error Reason' for it being invalid.
说明：我的数据库中有 200 多个无效对象，原因可能只是几个对象（其他是由于依赖性）。有什么方法可以选择对象名称和无效的“错误原因”。
回答by Lalit Kumar B
You could query [DBA/ALL/USER]_ERRORS. It describes current errors on all stored objects (views, procedures, functions, packages, and package bodies) owned by the current user.
您可以查询[DBA/ALL/USER]_ERRORS。它描述了当前用户拥有的所有存储对象（视图、过程、函数、包和包体）的当前错误。
Chose which view to query, depending on the privileges you have:
根据您拥有的权限选择要查询的视图：
- DBA_: All objects in the database
- ALL_: All objects owned by the user and on which the user has been granted privileges
- USER_: All objects owned by the user
- DBA_: 数据库中的所有对象
- ALL_：用户拥有的所有对象，并且用户已被授予权限
- USER_：用户拥有的所有对象
For example,
例如，
I create a procedurewith a compilation error, and I want to query the error details:
我创建了一个编译错误的过程，我想查询错误详细信息：
SQL> CREATE OR REPLACE PROCEDURE p
2 BEGIN
3 NULL
4 END;
5 /
Warning: Procedure created with compilation errors.
SQL>
SQL> SELECT NAME, TYPE, line, text FROM user_errors;
NAME TYPE LINE TEXT
----- ---------- ---------- --------------------------------------------------
P PROCEDURE 2 PLS-00103: Encountered the symbol "BEGIN" when exp
ecting one of the following:
( ; is with authid as cluster compress order us
ing compiled
wrapped external deterministic parallel_enable
pipelined
result_cache accessible
SQL>
Read more about it in documentation here
在此处的文档中阅读有关它的更多信息
回答by Wernfried Domscheit
You can check with this view:
您可以查看此视图：
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM ALL_OBJECTS
WHERE STATUS <> 'VALID';
resp. USER_OBJECTSor DBA_OBJECTS.
分别 USER_OBJECTS或DBA_OBJECTS。
ALL_ERRORSdoes not show all invalid objects.
ALL_ERRORS不显示所有无效对象。
Example:
例子：
CREATE TABLE tt (a NUMBER);
CREATE OR REPLACE VIEW ttt AS SELECT * FROM tt;
DROP TABLE tt;
SELECT * FROM USER_ERRORS;
no rows selected.
However, after selecting the view once you get an entry:
但是，在获得条目后选择视图后：
SELECT * FROM ttt;
Error at line 1
ORA-04063: view "xxxx.TTT" has errors
SELECT NAME, TYPE, TEXT FROM USER_ERRORS;
NAME TYPE TEXT
--------------------------------------------------------
TTT VIEW ORA-00942: table or view does not exist
