Toad World® Forums

Beta 13.1.0.19 - Minimum size button on Tempfile Definition dialog box


#1

Database: 11.2.0.4.180417

Client: 12.2 Instant Client

TEMP tablespace is comprised of three OMF named tempfiles in ASM. One of them was ~6GB in size. The minimum size button states that it can be shrunk to approxmiately 24654MB, which is considerably larger than its current size. I was able to shrink it to 2G with:

ALTER TABLESPACE temp SHRINK TEMPFILE ‘+DATAC1/ppst1/tempfile/temp.1371.979468821’ KEEP 2G;

toad_img.jpeg


#2

Hi Doug,

I made a 3-file TEMP tablespace on 11g ASM, trying to reproduce this, but so far not having any luck.

If you turn on spool SQL, you should be seeing queries similar to these:


– Session: JDORLON@ORCL11ASM
– Timestamp: 08:48:54.110
Select file_id, relative_fno, round(bytes / 1048576) size_mb, bytes / blocks block_size
FROM DBA_TEMP_FILES
WHERE file_name = :fname;
:fname(VARCHAR[44],IN/OUT)=’+DATA/orcl112asm/tempfile/temp.271.979720057’

– Rows fetched: 1
– Elapsed time: 0.012 seconds


– Session: JDORLON@ORCL11ASM
– Timestamp: 08:48:55.152
SELECT Round(SUM(bytes) / 1048576) freeable_mb
FROM V$TEMP_EXTENT_MAP
WHERE file_id = 2 – file_id from previous query.
AND block_id >= NVL((SELECT block_id + (bytes / 8192)
FROM V$TEMP_EXTENT_MAP
WHERE block_id = (SELECT MAX(block_id)
FROM V$TEMP_EXTENT_MAP
WHERE file_id = 2 – file_id from previous query.
AND OWNER <> 0)
AND file_id = 2), 0);
– Rows fetched: 1
– Elapsed time: 0.01 seconds

afterwards, the number we give you for min file size = Size_MB (from the first query) - freeable_MB (from the 2nd query).

I know you’ve shrunk it by now, so it may be too late, but are you seeing the same queries and does the query result agree with what Toad is telling you?

I don’t see anything wrong with the query, do you? Is it possible that your data dictionary was out of whack?


#3

John,

I exercised the Minimum Size button on a variety of databases and tempfiles today and also could not replicate the issue. The database in question has been dropped and re-cloned, so unfortunately will not be able to provide any detail other than what was originally reported.

Will provide more info if I ever encounter this issue again.

Thanks,
Doug