由9itemp表空間遭遇ORA-01652錯誤蔓延開來 zt

asword發表於2010-01-04
[@more@]

一套核心的rac生產系統，最近老遭遇ORA-01652無法擴臨展時表空間的錯誤，臨時表空間有60G，檢視使用率在96%左右.按說這麼打的temp表空間不應該存在不夠用的情況，懷疑是oracle沒有自動回收不再使用的臨時表空間，google也沒有很好的回收的辦法，大概總結了一下有這些辦法，並且蔓延開很多東西。

從我的測試來看：alter tablespace temp coalesce; alter tablespace temp default storage(pctincrease 1); alter tablespace temp permenant(不適用於create temporary tablespace 建立的臨時表空間); 這些方法對於LMT基本無效。

已知的代價較小的一種辦法是：給臨時表空間增加一個新的臨時檔案，然後刪掉原來的臨時檔案：

system@oracle>system@oracle> create temporary tablespace temp1
2 tempfile ‘g:orantdatabasetemp1a.dbf’ size 1M reuse
3 extent management local uniform size 64k
4 /

Tablespace created.

system@oracle> alter tablespace temp1
2 default storage (pctincrease 1)
3 /
alter tablespace temp1
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

system@oracle> alter tablespace temp1 permanent
2 /
alter tablespace temp1 permanent
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

system@oracle> alter tablespace temp1
2 add tempfile ‘g:orantdatabasetemp1b.dbf’ size 1M reuse
3 /

Tablespace altered.

system@oracle> alter database tempfile ‘g:orantdatabasetemp1a.dbf’ offline
2 /

Database altered.

system@oracle> alter database tempfile ‘g:orantdatabasetemp1a.dbf’ drop
2 /

Database altered.

1.重啟資料庫有可能釋放臨時表空間，可是生產庫的重啟談何容易。

2.新建臨時表空間替換，這個倒跟回收UNDO表空間相似，不過，核心生產上也需要謹慎操作，相關SQL：

SQL> create temporary tablespace temp2 tempfile ‘/dev/rdbdata_temp02′ size 10000M autoextend off;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp;

然後作業系統層面物理刪除檔案。

3.如果資料庫是11g的，可以shrink 臨時表空間,不能shrink其他表空間，（請注意，10g的新特性是shrink 表，非表空間）。其他版本可以coalesce表空間，先附上9i，10g，11g的修改表空間的語法：

1）.11g（R1和R2相同）的語法：
ALTER TABLESPACE tablespace
{ DEFAULT [ table_compression ] storage_clause
| MINIMUM EXTENT size_clause
| RESIZE size_clause
| COALESCE
| SHRINK SPACE [ KEEP size_clause]
| RENAME TO new_tablespace_name
| { BEGIN | END } BACKUP
| datafile_tempfile_clauses
| tablespace_logging_clauses
| tablespace_group_clause
| tablespace_state_clauses
| autoextend_clause
| flashback_mode_clause
| tablespace_retention_clause
} ;
2）.10g的語法：
ALTER TABLESPACE tablespace
{ DEFAULT
[ table_compression ] storage_clause
| MINIMUM EXTENT size_clause
| RESIZE size_clause
| COALESCE
| RENAME TO new_tablespace_name
| { BEGIN | END } BACKUP
| datafile_tempfile_clauses
| tablespace_logging_clauses
| tablespace_group_clause
| tablespace_state_clauses
| autoextend_clause
| flashback_mode_clause
| tablespace_retention_clause
} ;
3）.9i的語法：
ALTER TABLESPACE tablespace
{ datafile_tempfile_clauses
| DEFAULT [ data_segment_compression ] storage_clause
| MINIMUM EXTENT integer [ K | M ]
| ONLINE
| OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
| { BEGIN | END } BACKUP
| READ { ONLY | WRITE }
| PERMANENT| TEMPORARY
| COALESCE
| logging_clause
| [ NO ] FORCE LOGGING
} ;
官方文件的說法：

COALESCE

For each datafile in the tablespace, this clause combines all contiguous free extents into larger contiguous extents.

SHRINK SPACE Clause

This clause is valid only for temporary tablespaces. It lets you reduce the amount of space the tablespace is taking. In the optional KEEP clause, the size_clause defines the lower bound that a tablespace can be shrunk to. It is the opposite of MAXSIZE for an autoextensible tablespace. If you omit the KEEP clause, then the database will attempt to shrink the tablespace as much as possible as long as other tablespace storage attributes are satisfied.

11g：再次強調
SQL>alter tablespace temp shrink space;
11g，10g，9i，8i
SQL>alter tablespace temp coalesce;
這個也有可能報錯：
SQL> alter tablespace temp coalesce;
alter tablespace temp coalesce
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
貌似這條語句不適用於臨時表空間，對於永久表空間有效。這個方法看來無效，不過網上好多這樣的文章，這些人都沒有經過驗證就寫出來，簡直誤人子弟。
4.先找出系統中正在使用排序段的session：
SELECT se.username,sid,serial#,sql_address,machine,program,tablespace,segtype,contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr;
看情況kill掉？？
SQL>alter system kill session ’sid,serial#’;（慎用）
5.metalink上給出的一種方法：
修改一下TEMP表空間的storage引數，改為非0引數。讓Smon程式觀注一下臨時段，從而達到清理和TEMP表空間的目的。
SQL>alter tablespace temp storage (pct increase 1);
執行這條語句有可能會遭遇一個錯誤：
===============
SQL> alter tablespace temp default storage(pctincrease 1);
alter tablespace temp default storage(pctincrease 1)
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
這個錯誤是由於臨時表空間是本地管理的，
==============
等SMON 自動coalesce之後再將引數改為0，
alter tablespace temp storage (pct increase 0);
===============================================
官方文件關於PCTINCREASE引數的解釋：
PCTINCREASE
Specify the percent by which the third and subsequent extents grow over the
preceding extent. The default value is 50, meaning that each subsequent extent is
50% larger than the preceding extent. The minimum value is 0, meaning all extents
after the first are the same size. The maximum value depends on your operating
system.
Oracle rounds the calculated size of each new extent to the nearest multiple of the
data block size.
If you change the value of the PCTINCREASE parameter (that is, if you specify it in
an ALTER statement), then Oracle calculates the size of the next extent using this
new value and the size of the most recently allocated extent.
Suggestion: If you wish to keep all extents the same size, you can
prevent SMON from coalescing extents by setting the value of
PCTINCREASE to 0. In general, Oracle Corporation recommends a
setting of 0 as a way to minimize fragmentation and avoid the
possibility of very large temporary segments during processing.
Restriction on PCTINCREASE You cannot specify PCTINCREASE for rollback
segments. Rollback segments always have a PCTINCREASE value of 0.
======================================================
6. 網上給出診斷事件的一種方法
確定TEMP表空間的ts#
SQL> select ts#, name from sys.ts$ ;TS# NAME
———- ————————————————————
0 SYSTEM
1 UNDOTBS1
2 TEMP
3 INDX
4 TOOLS
5 USERS
6 DKH_DATA
7 DKH_INDX
8 PHS_DATA
9 PHS_INDX
10 rows selected.

執行清理操作
SQL>alter session set events ‘immediate trace name DROP_SEGMENTS level 3′ ;

說明：
temp表空間的TS# 為 2*, So TS#+ 1= 3

*************************metalink上一篇文章ID:19047.1摘在這裡*************************
Error:  ORA-1652
Text:   unable to extend temp segment by %s in tablespace %s
------- -----------------------------------------------------------------------
Cause:  Failed to allocate an extent for temp segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
        files to the tablespace indicated or create the object in another
        tablespace.

*** Important: The notes below are for experienced users - See 

Explanation:
        This error is fairly self explanatory - we cannot get enough space for
        a temporary segment. The size reported in the error message is
        the number of contiguous free Oracle blocks that cannot be found
        in the listed tablespace.

  NOTE: A "temp segment" is not necessarily a SORT segment in a
        temporary tablespace.
        It is also used for temporary situations while creating or dropping
        objects like tables and indexes in permanent tablespaces.
        eg: When you perform a CREATE INDEX a TEMP segment is created
            to hold what will be the final permanent index data. This
            TEMP segment is converted to a real INDEX segment in the
            dictionary at the end of the CREATE INDEX operation. It remains
            a temp segment for the duration of the CREATE INDEX operation
            and so failures to extend it report ORA-1652 rather than an
            INDEX related space error.

	A TEMPORARY segment may be from:
                A SORT          Used for a SELECT or for DML/DDL

		CREATE INDEX	The index create performs a SORT in the users
				default TEMP tablespace and ALSO uses a TEMP
				segment to build the final index in the INDEX
				tablespace. Once the index build is complete
				the segment type is changed.

		CREATE PK CONSTRAINT

		ENABLE CONSTRAINT

		CREATE TABLE	New tables start out as TEMPORARY segments.
				Eg: If MINEXTENTS is > 1 or you issue
				    CREATE table as SELECT.

                Accessing a GLOBAL TEMPORARY TABLE
                                When you access a global temporary table
                                a TEMP segment is instantiated to hold the
                                temporary data.

	It is worth making sure the TEMP tablespace PCTINCREASE is 0 and
	that it has a sensible (large) storage clause to prevent fragmentation.
        For TEMPORARY temp tablespaces make sure both INITIAL and NEXT are
        set to large values as extent sizes are taken from the NEXT clause
        and not the INITIAL clause.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/82387/viewspace-1030254/，如需轉載，請註明出處，否則將追究法律責任。

相關文章