oracle shrink tempfile to release free space on the tempspace

background:  Received Disk capacity Warning on one of our oracle database servers,
So needs to check is the data increasing as expect or any abnormal case

1. Checked on the target Drive to check if any other files created   --checked--No
2. Checked the tablespace useage as follow
SQL> Select Tablespace_Name,Sum(bytes)/1024/1024/1024 tablespace_size from Dba_Segments Group By Tablespace_Name;

TABLESPACE_NAME                TABLESPACE_SIZE
------------------------------ ---------------
SYSAUX                              .263305664
LOB_TABLESPACE                      45.7285156
UNDOTBS1                            .014892578
USERS                               1.88330078
SYSTEM                              .316040039
The Tablespaces are all in normal size compared with their history

3. Checked the Logfile and also found nothing special on the Logfile

4. Check the TEMPFILE as follow

SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;

TABLESPACE_NAME                 CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS FREE_BLOCKS/TOTAL_BLOCKS
------------------------------- ------------- ------------ ----------- ----------- ------------------------
TEMP                                        7        12800        1280       11520                    .9

Found that the Free size on the TEMPFILE are 90%, then query out the details size info on the Tempspace as follow

SQL> SELECT TABLESPACE_NAME,FILE_ID,BYTES_USED/1024/1024/1024,BYTES_FREE/1024/1024/1024 FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                   FILE_ID BYTES_USED/1024/1024/1024 BYTES_FREE/1024/1024/1024
------------------------------ ---------- ------------------------- -------------------------
TEMP                                    1                .098632813                31.9003906

Around 32GB can be released from the Tempspace, then execute the follow command to release the Free space from the TempSpace

SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE 'D:\app\Vector\\oradata\perfect\temp01.dbf';
Tablespace altered.

Then query the Tempspace useage again as follow, the Free space are released...
SQL> SELECT TABLESPACE_NAME,FILE_ID,BYTES_USED/1024/1024/1024,BYTES_FREE/1024/1024/1024 FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                   FILE_ID BYTES_USED/1024/1024/1024 BYTES_FREE/1024/1024/1024
------------------------------ ---------- ------------------------- -------------------------
TEMP                                    1                .098625183                         0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27799221/viewspace-2085770/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27799221/viewspace-2085770/