How to Shrink Oracle tablespace temp

I went to shrink temp tablespace because the temp tablespace is too big.

[oracle@db9 datafile]$ ls -l
total 148531244
-rw-r-----. 1 oracle oinstall  1761615872 Oct  5 22:07 o1_mf_sysaux_kd4pczgd_.dbf
-rw-r-----. 1 oracle oinstall  4875886592 Oct  5 22:07 o1_mf_system_kd4pbl9t_.dbf
-rw-r-----. 1 oracle oinstall 34358697984 Oct  5 22:01 o1_mf_temp_kd4pg44w_.tmp
-rw-r-----. 1 oracle oinstall 27393007616 Oct  5 22:00 o1_mf_temp_kmdkq6dn_.tmp
...

Query to find then temp tablespace free size.

SQL> select tablespace_name, sum(tablespace_size)/1024/1024 total_size, sum(allocated_space)/1024/1024 used_space, sum(free_space)/1024/1024 space_free from dba_temp_free_space group by tablespace_name, tablespace_size, allocated_space, free_space;

TABLESPACE_NAME 	       TOTAL_SIZE USED_SPACE SPACE_FREE
------------------------------ ---------- ---------- ----------
TEMP			       58800.9844 58800.9844	  58791

shrink the temp tablesapce, it's will run a long time.

SQL> alter tablespace temp shrink space;

Tablespace altered.

but the file is not shrinking, when shrinking is done

[oracle@db9 ORCL]$ cd datafile/
[oracle@db9 datafile]$ ls -l
total 149872684
-rw-r-----. 1 oracle oinstall  1772101632 Oct  6 10:06 o1_mf_sysaux_kd4pczgd_.dbf
-rw-r-----. 1 oracle oinstall  4875886592 Oct  5 22:07 o1_mf_system_kd4pbl9t_.dbf
-rw-r-----. 1 oracle oinstall 34358689792 Oct  6 09:21 o1_mf_temp_kd4pg44w_.tmp
-rw-r-----. 1 oracle oinstall 27298627584 Oct  6 09:21 o1_mf_temp_kmdkq6dn_.tmp

find the tempfile location

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oradata/ORCL/datafile/o1_mf_temp_kd4pg44w_.tmp
/u01/app/oradata/ORCL/datafile/o1_mf_temp_kmdkq6dn_.tmp

shrink the tempfile

SQL> alter tablespace temp shrink tempfile '/u01/app/oradata/ORCL/datafile/o1_mf_temp_kmdkq6dn_.tmp' keep 1G;

Tablespace altered.

SQL> alter tablespace temp shrink tempfile '/u01/app/oradata/ORCL/datafile/o1_mf_temp_kd4pg44w_.tmp' keep 1G;

Tablespace altered.

tempfile was shrink

[oracle@db9 datafile]$ ls -l
total 91761744
-rw-r--r--. 1 oracle oinstall         106 Oct  6 10:11 afiedt.buf
-rw-r-----. 1 oracle oinstall  1772101632 Oct  6 10:29 o1_mf_sysaux_kd4pczgd_.dbf
-rw-r-----. 1 oracle oinstall  4875886592 Oct  6 10:19 o1_mf_system_kd4pbl9t_.dbf
-rw-r-----. 1 oracle oinstall  1074790400 Oct  6 10:29 o1_mf_temp_kd4pg44w_.tmp
-rw-r-----. 1 oracle oinstall  1074790400 Oct  6 10:13 o1_mf_temp_kmdkq6dn_.tmp

張貼留言

0 留言