Oracle数据库如何扩展temp临时表空间

Oracle数据库如何扩展temp临时表空间

第一步：查看临时表空间大小

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

---

第二步：查看临时表空间是否可以扩展

select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;

第三步：添加临时表空间

ALTER TABLESPACE TEMP ADD TEMPFILE'/data/u01/app/oracle/oradata/oradb/temp02.dbf' SIZE 5000m;