Re: 10053 Trace and OBJ$ (DBA_SEGMENTS)

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
>

Other related posts: