Anand,
When I do an explain plan on your query (logged in as sys), I see all the
stats (on 10.2.0.2)
On 2/5/07, Anand Rao <panandrao@xxxxxxxxx> wrote:
>
> Hi,
>
> just to add that even if i query without the recyclebin, the problem
> exists.
>
> SELECT NVL2(partition_name,
> segment_name || ':' || partition_name,
> segment_name)
> FROM user_segments
> WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND
> segment_name NOT IN (:p1,:p2,:p3,:p4,:p5)
>
>
> Also, bind variables or not, its the same result.
>
> thanks
> anand
>
> On 05/02/07, Anand Rao <panandrao@xxxxxxxxx> wrote:
> >
> > Folks,
> >
> > Has anyone come across this issue where,
> >
> > you run a simple query on dba_segments (or user_segments) and produce
> > a 10053 trace. The trace file does not contain the " BASE STATISTICAL
> > INFORMATION" section. The table and index stats are completely
> > missing. So, i don't see the #Rows:, #Blks:, AvgRowLen:, AvgLen:,
> > NDV:, Nulls:, Density: and so on...
> >
> > Dictionary stats have been gathered, i haven't gathered System stats.
> >
> > The query is,
> >
> > SELECT NVL2(partition_name,
> > segment_name || ':' || partition_name,
> > segment_name)
> > FROM user_segments
> > WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
> > AND
> > segment_name NOT IN
> > (SELECT object_name
> > FROM recyclebin bin) AND
> > segment_name NOT IN (:p1,:p2,:p3,:p4,:p5)
> > ORDER BY bytes DESC
> >
> >
> > Now, if i run another query based on some other non-dictionary based
> > tables (EMP, DEPT), then everything is fine.
> >
> > It seems to be an issue with some access rights to OBJ$ is what i can
> > figure out but what permission needs to be granted is the question. The user
> > is a OS authenticated user, OPS$DEV1 and has DBA privilege. Even if i
> > execute the same query as user ' oracle', the owner of the database,
> > it doesn't help.
> >
> > Oracle version is 10.2.0.1.0 on Sun Solaris 9.
> >
> > is there something fundamental i am missing here? RTFM...??? i did a
> > bit of Meta-linking, searching the list and RTFMs but could not find
> > anything in particular.
> >
> > thanks,
> >
> > anand
> >
>
>
