find all objects beyond certain blocks in a datafile?

From: Guang Mei <gmei_at_proteome.com>
Date: Fri, 14 Sep 2001 12:46:59 -0400
Message-ID: <Vrqo7.1172$v6.166440@news.shore.net>

Hi,

Because there is an Oracle bug in 805 on Sun with datafile larger than 2GB, I want to resize some of our index datafiles from 2048M down to 2000M. But I got:

SQL> alter database datafile '/oracle/u05/oradata/YPD/indexes13.dbf' resize 2000M;
alter database datafile '/oracle/u05/oradata/YPD/indexes13.dbf' resize 2000M
*
ERROR at line 1:
ORA-03297: file contains 13088 blocks of data beyond requested RESIZE value

Is there an easy way to find which indexes occuping blocks between 2000M and 2048M in this file? from DBA_segments, maybe?

I am thinking that if I can identify them, then I can

Drop the index
Resize datafile to 2000M
Re-create the index.

This would fix the problem I have now.

TIA. Guang Received on Fri Sep 14 2001 - 11:46:59 CDT