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
>
