Ishan Joshi, March 14, 2017 - 9:55 am UTC
Thanks Connor for your response for my query.
Yes, I can see the free space in both dba/user_free_space and dba/user_segments.
But my question is with truncate/drop partition operations, High Water Mark (HWM) also needs to be reduced to free space as it is doing for normal table truncate operation.
In my case, tablespace size is still higher though the segments are free. with that we are not able to resize the datafile.
I am checking the HWM with following query
select a.tablespace_name,CEIL( blocks*(a.blocksize)/1024/1024) "Current Size in MB",
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss. in MB",
CEIL( blocks*(a.blocksize)/1024/1024) -
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings in MB",
'alter database datafile '''|| FILE_NAME || ''' resize ' ||
ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100 || 'm;' "Command",a.autoextensible
from (select a.*, P.value blocksize from DBA_DATA_FILES a
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
where CEIL( blocks*(a.blocksize)/1024/1024) - CEIL( (NVL(HWM,1)*(a.blocksize))/1024/1024 ) >= 0
order by "Savings in MB" desc;
options to reduce HWM, Shrink table and moving the table to other tablespace. But these options are cost more time to reclaim space in my case.
March 15, 2017 - 2:42 am UTC
Well you didnt *ask* that :-) You asked about free space, and as I've shown in the example, you *do* get the free space back for re-use by the same or other segments as the case may be.
If you want to *shift* a segment so that the hwm on a datafile is lowered, then you must physically move each segment.
But you also said: "to free the space to utilize for next runs". If you are doing to reuse the space anyway, I would not bother with trying reduce a datafile size, because it surely just grow again anyway