10053 Trace and OBJ$ (DBA_SEGMENTS)
- From: "Anand Rao" <panandrao@xxxxxxxxx>
- To: Oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Feb 2007 09:07:37 +0530
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:
