oracle修改临时表空间

    今天查看 alert 日志，发现又告警信息

hu Nov 03 12:57:55 2011

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Thu Nov 03 12:57:55 2011

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Thu Nov 03 12:57:55 2011

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Thu Nov 03 13:01:02 2011

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Thu Nov 03 13:04:28 2011

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Thu Nov 03 13:17:07 2011

一看就是临时表空间满了，所以准备扩展临时表空间

先查看下默认的临时表空间是哪个

select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';

默认临时表空间是temp，路径是：E:\ORACDATA\TESTSXX\TEMP01.DBF

用alter database tempfile ‘E:\ORACDATA\TESTSXX\TEMP01.DBF’ resize 50000M

但出现报错：报错的原因是由于在当初建立这个临时表空间的时候设置了maxsize 参数，所以现在不能用resize

现在只能先建立个临时表空间

create temporary tablespace TEMPORARY_DATA1
TEMPFILE 'e:/oradata/testsxx/TEMPORARY_DATA02.DBF'
SIZE 50M
AUTOEXTEND ON
NEXT 50M MAXSIZE 30720M
EXTENT MANAGEMENT LOCAL ;

然后把新建立的 临时表空间 设为默认的临时表空间

alter database default temporary tablespace TEMPORARY_DATA1;

增加下哪些用户在使用临时表空间的语句：

Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,
tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hashvalue=su.sqlhash
and s.address=su.sqladdr
order by se.username,se.sid;

删除临时表空间语句：

drop tablespace tempspacename including contents and datafiles;