创建视图时生成ORA-00942错误

ORA-00942 error is generating while creating a view(创建视图时生成ORA-00942错误)

问题描述

我已经为主用户hfdora创建了一个名为cons_interrupted_data的新视图，并且该视图已经成功创建。但是，当我在将所有权限授予用户(CI)之后，尝试为同一数据库的另一个用户(CI)创建相同的视图时，会收到以下错误，

*OMS_Consumer

第13行出错： ORA-00942：表或视图不存在

用户hfdora和配置项都是同一数据库的一部分，此OMS_Consumer表位于数据库中

在创建视图之前，我已为用户CI授予以下权限

grant select on energization_info to cis;
grant select on trigger_info to cis;
grant select on oms_source to cis;
grant select on oms_consumer to cis;
grant connect,resource,dba to cis;

My sql query to create the view,

>CREATE OR REPLACE VIEW CONS_INTERRUPTED_DATA AS
    SELECT
        trigger_info_A.b1 AS FDR_RMU_OFF_B1, trigger_info_A.b2 AS FDR_RMU_OFF_B2,
        trigger_info_A.B3TEXT AS FDR_RMU_OFF_B3TEXT, trigger_info_A.elem AS FDR_RMU_OFF_ELEM,
        trigger_info_B.b1 AS FDR_RMU_RESTORE_B1, trigger_info_B.b2 AS FDR_RMU_RESTORE_B2,
        trigger_info_B.B3TEXT AS FDR_RMU_RESTORE_B3TEXT,
        trigger_info_B.elem AS FDR_RMU_RESTORE_ELEM,
        oms_consumer.consumer_code, energization_info.b1 AS AFFECTED_B1,
        energization_info.b2 AS AFFECTED_B2, energization_info.b3text AS AFFECTED_B3TEXT,
        to_char(energization_info.deenergized_date, 'DD-MM-YYYY Hh24:MI:SS') AS DEENERGIZED_DATE,
        to_char(energization_info.energized_date, 'DD-MM-YYYY Hh24:MI:SS') AS ENERGIZED_DATE,
        trigger_info_A.comments AS KEY
    FROM
        energization_info, 
        trigger_info trigger_info_A,
        trigger_info trigger_info_B, 
        oms_consumer
    WHERE
        (energization_info.trigger_number = trigger_info_A.trigger_number)
        AND (energization_info.ENERGIZED_TRIGGER_NUMBER = trigger_info_B.trigger_number)
        AND (energization_info.b1 = oms_consumer.B1NAME 
             AND energization_info.b2 = oms_consumer.B2NAME 
             AND energization_info.b3 = oms_consumer.B3NAME)
    WITH READ ONLY;

推荐答案

我的问题已解决。：-)

我做了以下更改，

FROM
        hfdora.energization_info, 
        hfdora.trigger_info trigger_info_A,
        hfdora.trigger_info trigger_info_B, 
        hfdora.oms_consumer

现在为用户CI创建了相同的视图。

这篇关于创建视图时生成ORA-00942错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！