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#';

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