作者： dean

如题：在truncate table后，着手resize(小) tablesapce,报以上信息。

Metalink:(因为表的高水平标志没有被释放)

fact: Oracle Server – Enterprise Edition
symptom: Resizing a tablespace returns error
symptom: ORA-03297: file contains %s blocks of data beyond requested RESIZE
value
cause: There are extents allocated in the datafile beyond the size the user
wanted to resize the file to.

fix:

Use the following SELECT to find the extents (and their segments-objects) that
are placed beyond the desired size in the tablespace’s file:

SELECT owner, segment_name, segment_type, tablespace_name, file_id,
((block_id+1)*(SELECT value FROM v$parameter
WHERE UPPER(name)=’DB_BLOCK_SIZE’)+BYTES) end_of_extent_is_at_this_byte
FROM dba_extents
WHERE ((block_id+1)*(SELECT value FROM v$parameter
WHERE UPPER(name)=’DB_BLOCK_SIZE’)+BYTES) > (*1024*
1024)
AND tablespace_name=’‘
ORDER BY file_id, end_of_extent_is_at_this_byte;

Just substitute the and for
their actual values.

To receive only the objects that have extents beyond the size in question, use
the following query:

SELECT DISTINCT owner, segment_name, segment_type, tablespace_name, file_id
FROM dba_extents
WHERE ((block_id+1)*(SELECT value FROM v$parameter
WHERE UPPER(name)=’DB_BLOCK_SIZE’)+BYTES) > (*1024*
1024)
AND tablespace_name=’‘
ORDER BY file_id, owner, segment_name, segment_type;

You will be able to resize the file to the desired size if this SELECT returns
0 rows.
So you have to drop the selected segments (tables or indexes) to be able to
resize the datafile. Before dropping the objects you can export them or move
them to another tablespace.