ORA-03297: file contains used data beyond requested RESIZE value

-- QUERY 1 - datafile Allocated space,free space,High Water Mark location,%Free and resizeable MB

select

            tablespace_name,

            file_id,

            file_name DATA_FILE_NAME,

            Allocated_MBYTES,

            High_Water_Mark_MBYTES,

            FREE_MBYTES,

            trunc((FREE_MBYTES/Allocated_MBYTES)*100,2) "% Free",

            trunc(Allocated_MBYTES-High_Water_Mark_MBYTES,2) Resizeble

   from

   (

      select

           ddf.tablespace_name tablespace_name,

           ddf.file_id file_id,

           ddf.file_name file_name,

           ddf.bytes/1024/1024 Allocated_MBYTES,

           trunc((ex.hwm*(dt.block_size))/1024/1024,2) High_Water_Mark_MBYTES,

           FREE_MBYTES

      from

           dba_data_files ddf,

           dba_tablespaces dt,

      (

           select file_id, sum(bytes/1024/1024) FREE_MBYTES

           from dba_free_space

           group by file_id

      ) free,

      (

           select file_id, max(block_id+blocks) hwm

           from dba_extents

           group by file_id

      ) ex

      where ddf.file_id = ex.file_id

      and ddf.tablespace_name = dt.tablespace_name

      and ddf.file_id = free.file_id (+)

      order by ddf.tablespace_name, ddf.file_id

    );


-- QUERY 2 - location of segments within a datafile (Provide file_id of the Tablespace)

 

   select

                file_name data_file_name,

                segment_type,

                owner||'.'||segment_name segment_name,

                partition_name,

                block_id,

                blockId_Mbytes

        from

        (

         select

              de.owner owner,

              de.segment_name segment_name,

              de.segment_type segment_type,

              de.block_id block_id,

              DE.PARTITION_NAME partition_name,

              ddf.file_name file_name,

              trunc((de.block_id*(dt.block_size))/1024/1024,2) blockId_Mbytes

      from

              dba_extents de, dba_data_files ddf, dba_tablespaces dt

              where ddf.file_id = &file_id

              and ddf.file_id = de.file_id

              and ddf.tablespace_name = dt.tablespace_name

              order by de.block_id desc

      )

      where rownum <= 100;



To re-size the DATAFILE the only way is to lower the High Water Mark. This can be done by moving extents to other Tablespace or the same Tablespace. Check the size of Tablesapce before moving.  In production database it’s not easy to move table, LOB etc singe many user can accessing these objects. Index can be move by rebuilding online in production database. After moving the tables it’s necessary to rebuild the UNUSABLE indexes on the tables.

Comments