Invalid View cannot be compiled-CodePudding

I have an invaild view on my PROD system:

PROD.CLIENT

I found out that is invalid with following statement:

select
    owner c1,
    object_type c3,
    object_name c2
from
    dba_objects
where
    status != 'VALID'
order by
    owner,
    object_type;

I tried recompiling it but when I try the first statement it still shows up:

ALTER VIEW PROD.CLIENT COMPILE;

I also tried searching for user errors, but there are none:

SELECT * FROM user_errors;

How can I find the problem or error with my invalid view?

CodePudding user response：

SELECT * FROM user_errors;

Will only find errors belonging to your current user.

You are re-compiling a view belonging to the PROD user and if that is not the user you are connected as then you will not see the errors.

Instead, you can use:

SELECT * FROM all_errors WHERE owner='PROD' and name = 'CLIENT';

CodePudding user response：

You should CREATE VIEW :

 CREATE VIEW example AS
 select
    owner c1,
    object_type c3,
    object_name c2
from
    dba_objects
where
    status != 'VALID'
order by
    owner,
    object_type;
--- and then 
SELECT * FROM example; -- call view