Re: find all objects beyond certain blocks in a datafile?

"Guang Mei" <gmei_at_proteome.com> wrote in message news:Vrqo7.1172$v6.166440_at_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
>
> 1. Drop the index
> 2. Resize datafile to 2000M
> 3. Re-create the index.
>
> This would fix the problem I have now.
>
> TIA.
>
> Guang
>
>

This info can be retrieved using dba_extents, which does show a file_id and a block_id

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Fri Sep 14 2001 - 12:09:25 CDT