ORA-03297

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

SQL> select bytes/1024 from dba_data_files where file_id = 6;

BYTES/1024
----------
7168

SQL> select sum(bytes/1024) from dba_segments where tablespace_name = 'EXAMPLE';


SUM(BYTES/1024)
---------------
5440

SQL> alter database
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\example01.dbf' resize 5500k
;
alter database
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> ed
Wrote file afiedt.buf

1 select
2 a.file_name,
3 a.bytes file_size_in_bytes,
4 (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
5 a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
6 from dba_data_files a,
7 (select file_id,max(block_id) maximum
8 from dba_extents
9 group by file_id) b,
10 dba_extents c
11 where a.file_id = b.file_id
12 and c.file_id = b.file_id
13 and c.block_id = b.maximum
14* and c.tablespace_name = 'EXAMPLE'
SQL> /
Enter value for _block_size: 8192
old 4: (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
new 4: (c.block_id+(c.blocks-1)) * 8192 HWM_BYTES,
Enter value for _block_size: 8192
old 5: a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
new 5: a.bytes - ((c.block_id+(c.blocks-1)) * 8192) SAVING

FILE_NAME
--------------------------------------------------------------------------------

FILE_SIZE_IN_BYTES HWM_BYTES SAVING
------------------ ---------- ----------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB02\EXAMPLE01.DBF
7340032 6356992 983040


SQL> alter database
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\example01.dbf' resize 63569
92;

Database altered.