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_at_gmail.com> 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_at_gmail.com> 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
> >
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 05 2007 - 03:18:50 CST
