1、先查看表空间
@H_301_6@SELECTD.TABLESPACE_NAME"数据库文件名称",SPACE||'M'"总空间(M)",BLOCKS"总BLOCKS",SPACE-NVL(FREE_SPACE,0)||'M'"已用空间(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)||'%'"已用率(%)",FREE_SPACE||'M'"空闲空间(M)" FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKS FROMDBA_DATA_FILES GROUPBYTABLESPACE_NAME)D,(SELECTTABLESPACE_NAME,2) FREE_SPACE FROMDBA_FREE_SPACE GROUPBYTABLESPACE_NAME)F WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+) UNIONALL --如果有临时表空间 SELECTD.TABLESPACE_NAME,BLOCKSSUM_BLOCKS,USED_SPACE||'M'"已用空间(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)||'%' "已用率(%)",NVL(FREE_SPACE,0)||'M'"空闲空间(M)" FROM(SELECTTABLESPACE_NAME,SUM(BLOCKS)BLOCKS FROMDBA_TEMP_FILES GROUPBYTABLESPACE_NAME)D,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROMV$TEMP_SPACE_HEADER GROUPBYTABLESPACE_NAME)F WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+) ORDERBY1;2、再查看temp文件位置
3、创建temp2文件
@H_301_6@CREATETEMPORARYTABLESPACETEMP02 TEMPFILE'D:/APP/ADMINISTRATOR/ORADATA/ORCL/TEMP02.DBF' SIZE1024MAUTOEXTENDONNEXT30720MMAXSIZEUNLIMITED; --size是固定大小autoextendonnext为最大扩展大小 @H_301_6@ALTERDATABASEDEFAULTTEMPORARYTABLESPACETEMP02;5、查看是否有会话链接
参考
http://blog.csdn.net/lmalds/article/details/33725657