Forum Stats

  • 3,759,067 Users
  • 2,251,495 Discussions
  • 7,870,480 Comments

Discussions

Schema and Privileges

elmousa68
elmousa68 Member Posts: 180 Blue Ribbon
edited Nov 17, 2014 7:32AM in General Database Discussions

We have an table called table1 in the SSR schema. We have another schema called POVER where if the command "desc pover.table1" is executed (under user pover), it runs and returns the description of the table. However, if the command "select count(*) from pover.table1" is executed (again under user pover) , an error occurs "table or view does not exist". We are using Oracle database 11.2.

if we issue the command "desc ssr.table1" (under user pover), it runs correctly and the command returns the description of the table. and if we issue the command "select count(*) from ssr.table1" (under user pover) the command is executed correctly.

What is the cause of this behavior?

Tagged:
c57adb96-b496-47bf-86bc-315f472437bf

Answers

  • EdStevens
    EdStevens Member Posts: 28,467 Gold Crown

    You describe what you think you see, now prove it.

    At a sqlplus prompt, and connected as plover, issue the following:

    set echo on feedback on verify on

    show user

    desc pover.table1

    select count(*) from pover.table1

    COPY the entire session and PASTE back into your reply.

  • while logged onto the DB as user POVER issue the following SQL

    SELECT * FROM ALL _OBJECTS WHERE OBJECT_NAME =  'TABLE1';

    post the results from SQL above.

  • elmousa68
    elmousa68 Member Posts: 180 Blue Ribbon

    SQL> R

      1* SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME =  'POVER_RECOM'

    OWNER                          OBJECT_NAME                                     

    ------------------------------ ------------------------------                  

    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE           

    ------------------------------ ---------- -------------- -------------------   

    CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE               

    --------- --------- ------------------- ------- - - - ----------               

    EDITION_NAME                                                                   

    ------------------------------                                                 

    PUBLIC                         POVER_RECOM                                     

                                       106747                SYNONYM               

    26-SEP-12 13-AUG-14 2014-08-13:18:14:51 VALID   N N N          1               

                                                                                   

                                                                                   

    OWNER                          OBJECT_NAME                                     

    ------------------------------ ------------------------------                  

    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE           

    ------------------------------ ---------- -------------- -------------------   

    CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE               

    --------- --------- ------------------- ------- - - - ----------               

    EDITION_NAME                                                                   

    ------------------------------                                                 

    RSS                            POVER_RECOM                                     

                                       245234         245234 TABLE                 

    13-AUG-14 16-NOV-14 2014-08-13:18:02:37 VALID   N N N          1               

                                                                                   

                                                                                   

    2 rows selected.

    SQL> SPOOL OFF

  • New Roots
    New Roots Member Posts: 184 Blue Ribbon

    Hi,

    Please check the synonym created on that table.

    Regards,

    S.Balraj

This discussion has been closed.