Database Administration (MOSC)

MOSC Banner

Size of "TOTAL_BLOCKS" getting small in v$sort_segment

edited Jul 3, 2014 5:00AM in Database Administration (MOSC) 4 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center