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