Re: Problems with dba_segments view

Which specific version of Oracle ?

How many tablespaces do you have, and
how many of them are locally managed.

Have you dropped any locally managed
tablespaces, or made them read-only.

Do you have any trouble with dba_extents ? For each object in the tablespace where
the 'blocking block' exists, can you check dba_segments for each segment in turn,
and dba_extents for each segment in turn.

How are you demonstrating that the session is stuck on 'buffer busy waits' ? By using v$session_wait, or by setting event 10046 -

The dbms_space_admin functions are documented in $ORACLE_HOME/rdbms/admin/dbmsspc.sql, by the way.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Kent Sauder wrote in message ...


>We are having a problem using the dba_segments view.  When we attempt to

>query the view, it locks with "buffer busy waits".  If we query

>v$waitstat, this session is the only one attempting to access the

>specified file and block.  Since dba_segments is based on the view

>sys_dba_segs, I attempted to query that view.  It works normally.

>Everything else in the database appears to be functioning normally and

>we aren't receiving any alert.log messages.

>

>So far, I've tracked the problem down to two clauses within the

>dba_segments view with both making calls to

>dbms_space_admin.segment_number_blocks.  Naturally, this procedure is

>not documented.

>

>Any ideas as to what might be causing this and more importantly, how to

>fix it?

>