Scripts to Get list of objects to be moved from a data file for space released
When you specify the size and file id during execution of the script then script will provide the list of objects for moving into new tablespace to reduce the current Size to size you mentioned in Script.
After you move you can resize the datafile with
alter datafile resize command.
Follow the following link for more detail:
So, the solution is to relocate used blocks to free gaps between for reduce the highwater marks of datafiles.
We can achieve this by several ways:
ALTER TABLE …. MOVE or INDEX REDUILD;
use DBMS_REDIFINITION package;
Export table, purge it and import again with the help of DataPump.
Following Script give the list of objects, which need to be relocated/moved to make datafile reduce possible size as needed.
The following query show such objects:
You can mention the file id and size upto which you want to reduce:
V_FILE_ID := &FILE_ID;
V_RESIZE_SIZE := &RESIZE_FILE_TO;
WHERE FILE# = V_FILE_ID;
'OBJECTS IN FILE '
|| ' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '
|| ' BYTES');
IN ( SELECT DISTINCT
|| ' - OBJECT TYPE = '
WHERE (block_id + blocks - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE
AND FILE_ID = V_FILE_ID
ORDER BY 1)