ORA-03297 file contains used data beyond requested RESIZE valueCause: Some portion of the file in the region to be trimmed is currently in use by a database object.Action: Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed
I have a tablespace with 1 datafile, 10Mb large.
My blocksize is 8196 bytes, so I have 1280 blocks 1 select blocks, bytes/1024/1024
2 from dba_data_files
3* where tablespace_name='TOOLS'
SQL> / BLOCKS BYTES/1024/1024
---------- ---------------
1280 10 Now, what is the largest minimum size I can shrink my datafile to?
I have to query dba_extents for that: 1 select segment_name, max(block_id)
2 from dba_extents
3 where tablespace_name='TOOLS'
4* group by segment_name order by 2 ...
RCVER_VERSION_UNIQUE 769 This means I have data in block 769 of 1280 blocks, so I can shrink back to 769 * 8196 (blocksize) = 6,3 Mb
You can of course use file_id instead of tablespace_name... SQL> alter database datafile 'C:\ORACLE\ORADATA\ORA920\TOOLS01.DBF' resize 6M;
alter database datafile 'C:\ORACLE\ORADATA\ORA920\TOOLS01.DBF' resize 6M
ORA-03297 file contains used data beyond requested RESIZE value SQL> alter database datafile 'C:\ORACLE\ORADATA\ORA920\TOOLS01.DBF' resize 7M;
--> succeeded If this segment is an index segment you can try to rebuild the index:
alter index <indexname> rebuild. If this segment is a table you can move it around:
alter table <tablename> move tablespace <tablespacename>;
and back
alter table <tablename> move tablespace <original_tablespace>; Do Not forget to rebuild the indexes after this, they will be in an UNUSABLE state. You can also move the table by exporting/importing it.