This discussion is archived
1 Reply Latest reply: Aug 20, 2012 3:40 AM by BillyVerreynne RSS

partitions not showing up in all_objects?

thePetester Newbie
Currently Being Moderated
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?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points