Re: 10053 Trace and OBJ$ (DBA_SEGMENTS)

  • From: "Anand Rao" <panandrao@xxxxxxxxx>
  • To: Oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 5 Feb 2007 09:12:39 +0530

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:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2007