由9itemp表空间遭遇ORA-01652错误蔓延开来 zt

http://www.dbadoc.com/2009/11/9i_temp_ora_1652[@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 Note:22080.1

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/82387/viewspace-1030254/