How to reclaim space from temporary tablespace

DBA Notes: 2011/09/23

Cheng Li

 

How to resize temporary tablespace

 

We got an alert about temporary tablespace as following:

 

For the alert level, it is now 80%. If you need any adjustment of the alert level, please raise a change request.

Filesystem    1024-blocks      Free      %Used    Iused          %Iused   Mounted on

/dev/workarealv    20971520   3994488   81%   179458      16%              /workarea

 

Temporary tablespace is majorly used for sorting, index management, view selecting. When PGA process sorting, if SORT_AREA_SIZE is not enough, temporary tablespace will be allocated. After sorting finished, temporary tablespace will be automatically managed and freed by SMON. But, DBA should manually reclaim data file space related to temporary tablespace.

 

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;

alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

 

Oops! This is cause by high water mark. Please reference how to calculate HWM. You can do as following:

 

SQL> SELECT tablespace_name, file_name, bytes

  2  FROM dba_temp_files WHERE tablespace_name = 'TEMP';

 

TABLESPACE_NAME   FILE_NAME                                 BYTES

----------------- -------------------------------- --------------

TEMP              /u02/oradata/TESTDB/temp01.dbf   13,107,200,000

 

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp02.dbf' SIZE 512m

  2  AUTOEXTEND ON NEXT 250m MAXSIZE 2014M;

 

Tablespace altered.

 

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES;

 

Database altered.

 

 

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m

  2  AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

 

Tablespace altered.

 

Other Solutions:

 

1)      If possible, restart the database

2)  SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;

3)      Kill Session

SELECT username,
       sid,
       serial#,
       sql_address,
       machine,
       program,
       tablespace,
       segtype,
       contents
  FROM v$session se,
       v$sort_usage su
 WHERE se.saddr=su.session_addr; 

SQL>Alter system kill session 'sid,serial#';

 

Reference:

Calcualte the space can be resized from temporary file on apply that have only one tempfile

select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
     (select tmsize.maxblk*bk.value/1024/1024 siz from
         (select nvl(max(segblk#),128) maxblk  from v$sort_usage) tmsize,
         (select value From v$parameter where name = 'db_block_size') bk) b

 

http://www.xifenfei.com/1064.html

http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/d_stats.htm#i997763

http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

http://blog.csdn.net/wonder4/article/details/600089

8ebf35c4gw1dl75t0e33ej.jpg

66ad8543jw1dl7k9g77t5j.jpg

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

转载于:http://blog.itpub.net/26136400/viewspace-708383/