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