Size of "TOTAL_BLOCKS" getting small in v$sort_segment
We have scheduled jobs on checking the current usage of temporary tablespace as follows, but we found the size of "TOTAL_BLOCKS" were getting smaller.
Why? Isn't it allocated and will not be deallocated and I expect the size of "TOTAL_BLOCKS" shall be increasing or getting steady?
(Please see the attached collected data for the trend of "TOTAL_BLOCKS")
CREATE OR REPLACE PROCEDURE TEMP_SEG_SPACE_TREND_INSERT IS
BEGIN
insert into HKHA.TEMP_SEG_SPACE_TREND
select sysdate as date_time, INST_ID, tablespace_name,
total_blocks,
used_blocks,
free_blocks,
round((free_blocks / total_blocks) * 100,1) as free_percent