一套核心的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的修改表空间的语法:
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.
alter tablespace temp coalesce
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr;
SQL>alter tablespace temp storage (pct increase 1);
alter tablespace temp default storage(pctincrease 1)
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
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.
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.
segments. Rollback segments always have a PCTINCREASE value of 0.
确定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
执行清理操作
SQL>alter session set events ‘immediate trace name DROP_SEGMENTS level 3′ ;
说明:
temp表空间的TS# 为 2*, So TS#+ 1= 3
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/