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

Guang Mei wrote:
>
> 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

select max(block_id+blocks) from dba_extents where ...

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."