Useful scripts: Get a list of objects to be moved for a data file resize
Every DBA from time to time wants to reduce a size of a data file, thinking ‘There is a lot of free space in datafile, why don’t to resize it?’ But the secret is that shrinking tables is not very helpful in this case — datafile least size will be limited to the block, where the last table’s/index’s block resides.
So, the solution is to relocate used blocks to free gaps between blocks.
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.
But prior we need to get a list of objects, which need to be relocated to make datafile reduce possible. The following query show such objects:
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)