Re: 10053 Trace and OBJ$ (DBA_SEGMENTS)

Hmm, thats rather interesting. it did notice that the statistics are presented in an EXPLAIN PLAN. then whats the issue with 10053?

thanks
anand

On 05/02/07, Ghassan Salem <salem.ghassan_at_gmail.com > wrote:
>
> 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