resize datafiles

Hi,
after that i drop some users, i want decrease the size of the only datafile of the tablespace that contains my old users.
Even that the space used was decrease, if i try to decrease the size of datafile, at a value major than space used, throw me the exception:

ORA-03297: il file contiene dati usati oltre valore RESIZE richiesto.

Thank you,
Max

before i resize the datafile/tablespace I usually:
alter tablespace (tablespace_name) coalesce;

then i use storage manager to resize my datafiles/tablespaces. The GUI is a lot quicker i think.

anyone else have suggestions?

- magnus

Maybe the coalesce will take care of it, but the message says there is a used block between the end of the shrink-to size and the current size.

You might need to check dba_extents for the file_id and see if anything really is there.

Hi

To know the size to decrease the datafile
1) Make the difference between
the max(BLOCK_ID) from dba_free_space
and the max(BLOCK_ID) from dba_extents
for the the FILE_ID=? --->A

2) Find the number of Oracle blocks in the datafile FILE_ID=?
--->B

3) If A is positive, make the difference between B and the
max(BLOCK_ID) from dba_free_spacde
Then add at least 4 blocks
Mutiplie you result by the BLOCK_SIZE
and then you've got the size to reduce

Your observations are correct and exhaustive.

Thank you,
Max