无法扩展temp段!
在pl/sql developer 中执行如下sql语句:
SELECT distinct tbl_NodeStatus.ActualhandleUserIDArray,tbl_NodeStatus.Enddate,tbl_Project.djlx,Tbl_Project.DWDM,Tbl_Project.QXDM,tbl_Project.ProjectID,tbl_Project.ProjectTypeID,tbl_Project.ProjectName,to_char(tbl_Project.ProjectBeginTime, 'yyyy-mm-dd ') ProjectBeginTime,
tbl_Project.IsHalted,tbl_Project.IsSupervised,
tbl_Project.ProjectRestrictTime,
tbl_UserRole.UserID,-1 as AuthorizerID,-1 as ConsignerID,d_Exoticrecord.Zdbh
FROM tbl_UserRole INNER JOIN
tbl_RolePowers ON tbl_UserRole.RoleID = tbl_RolePowers.RoleID AND
tbl_UserRole.ProjectTypeID = tbl_RolePowers.ProjectTypeID AND
tbl_UserRole.VersionID = tbl_RolePowers.VersionID INNER JOIN
tbl_Powers ON
tbl_RolePowers.ProjectTypeID = tbl_Powers.ProjectTypeID AND
tbl_RolePowers.VersionID = tbl_Powers.VersionID AND
tbl_RolePowers.PowersID = tbl_Powers.PowersID INNER JOIN
tbl_Project ON tbl_Powers.VersionID = tbl_Project.VersionID AND
tbl_Powers.ProjectTypeID = tbl_Project.ProjectTypeID INNER JOIN
tbl_NodeStatus ON tbl_Project.ProjectID = tbl_NodeStatus.ProjectID AND
tbl_Powers.NodeID = tbl_NodeStatus.NodeID INNER JOIN
d_Exoticrecord ON tbl_Project.ProjectID = d_Exoticrecord.ProjectID
WHERE (tbl_Project.Ispigeonholed=0) and
(tbl_NodeStatus.NodeState = 2)
时出现错误提示框:
ORA-01652:无法通过128(在表空间TEMP中)扩展temp段。
ORA-27059:skgfrsz 无法减少文件大小
OSD-04005:SetFilePointer()失败,无法读取文件
O/S-ERROR:(OS 112)磁盘空间不足
不知如何解决,敬请指点!
------解决方案--------------------你的SQL要排序,用到了temp段,temp段空间不够时,temp段有自动扩展选项
oracle自动扩展temp段时,操作系统报没有空间了。
解决:
在操作系统里搞点空间
或优化SQL,减少排序类操作
------解决方案--------------------temp表空间不够了,并且磁盘也已经没有空间可以扩展了
------解决方案--------------------教你怎样修改缺省temp表空间
create temporary tablespace temptest tempfile '/dev/vgqrya01/rlv_08_temp_002 ';
alter database default temporary tablespace temptest;
drop tablespace temp;
create temporary tablespace temp tempfile '/dev/vgqrya01/rlv_08_temp_001 ';
alter database default temporary tablespace temp;
drop tablespace temptest;
------解决方案--------------------哦,那要修改操作系统的限制,允许大文件,在文件系统选项,