1 Reply Latest reply: Aug 20, 2012 5:40 AM by Billy~Verreynne RSS

    partitions not showing up in all_objects?

    thePetester
      Hello,

      Has anyone seen this before? The dw team has an appid that has read/write privs to a partitioned table...

      logged in as appid adshX, I am able to select from stgadm3.TMUTL_FND_POSN partition(p1)

      1* select count(*) from stgadm3.TMUTL_FND_POSN partition(p1)
      SQL> /

      COUNT(*)
      ----------
      927282

      again, logged in as appid adshX, I am able to select from all_objects, but only the table name is returned...
      SQL> select owner, object_type, object_name, subobject_name from all_objects where owner = 'STGADM3' AND object_name='TMUTL_FND_POSN';

      OWNER OBJECT_TYPE OBJECT_NAME SUBOBJECT_NAME
      ------------------------------ ------------------- ------------------------------ ------------------------------
      STGADM3 TABLE TMUTL_FND_POSN

      if logged in as the sys or system, the table AND partitions are returned from all_objectds.
      OWNER OBJECT_TYPE OBJECT_NAME SUBOBJECT_NAME
      ------------------------------ ------------------- ------------------------------ ------------------------------
      STGADM3 TABLE PARTITION TMUTL_FND_POSN P1
      STGADM3 TABLE PARTITION TMUTL_FND_POSN P_MINUS_1
      STGADM3 TABLE TMUTL_FND_POSN

      Also, the adshX ids have access to the dba_* views, but that returns the correct results.
      OWNER OBJECT_TYPE OBJECT_NAME SUBOBJECT_NAME
      ------------------------------ ------------------- ------------------------------ ------------------------------
      STGADM3 TABLE PARTITION TMUTL_FND_POSN P1
      STGADM3 TABLE PARTITION TMUTL_FND_POSN P_MINUS_1
      STGADM3 TABLE TMUTL_FND_POSN

      My initial thought was that some priv was missing, but since they can read/write/create tables and partitions with this id, I don't know what other priv they would need in order for the information to show up.

      This is an oracle 10g db.

      Thanks, Pete
        • 1. Re: partitions not showing up in all_objects?
          Billy~Verreynne
          This question has nothing to do with Oracle object orientated and object relational concepts and issues - the subject matter of this forum.

          Post your question in {forum:id=61} and use the <b>
          </b> tags for properly formatting your SQL code and output.