This discussion is archived
11 Replies Latest reply: Feb 18, 2013 8:58 AM by Nicolas.Gasparotto RSS

Shrinking of Temp Tablespace doesnot shrink tempfile at OS level

Step_Into_Oracle_DBA Explorer
Currently Being Moderated
I want to test the new 11g feature of shrinking tempfile. My tempfile size is 32GB and i have tried shrinking it to 5gb but it doesnt resize the tempfile at OS level. Still file size remains same.

Please suggest here.
select tablespace_name,round(bytes/1048576/1024,2) "size GB",file_id,file_name,round(maxbytes/1048576/1024,2) "Max in GB",autoextensible from   dba_temp_files where  tablespace_name like 'TEMP';
SQL>
TABLESPACE_NAME                   size GB    FILE_ID FILE_NAME                                                               Max in GB AUT
------------------------------ ---------- ---------- ---------------------------------------------------------------------- ---------- ---
TEMP                                31.99          1 +mydb_DATA/mydb/tempfile/temp.264.798205781                                32 YES

SQL> alter tablespace temp shrink space;

Tablespace altered.

SQL> select tablespace_name,round(bytes/1048576/1024,2) "size GB",file_id,file_name,round(maxbytes/1048576/1024,2) "Max in GB",autoextensible from   dba_temp_files where  tablespace_name like 'TEMP';

TABLESPACE_NAME                   size GB    FILE_ID FILE_NAME                                                               Max in GB AUT
------------------------------ ---------- ---------- ---------------------------------------------------------------------- ---------- ---
TEMP                                31.99          1 +mydb_DATA/mydb/tempfile/temp.264.798205781                                32 YES

SQL> select TABLESPACE_NAME,round((Tablespace_size)/1048576/1024,2) "Total in GB",(allocated_space)/1048476/1024 "Used in GB",round((FREE_Space)/1048576/1024,2) "Free in GB"
from dba_temp_free_space;  2

TABLESPACE_NAME                Total in GB Used in GB Free in GB
------------------------------ ----------- ---------- ----------
TEMP                                 31.99 .049801808      31.96
Still File size remains 32GB at OS level.

Edited by: Step Into Oracle DBA on Feb 11, 2013 6:25 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points