1、先查看表空间
SELECT D.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 ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2)
FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
--如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "总空间(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "已用空间(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%'
"已用率(%)",
NVL (FREE_SPACE, 0) || 'M' "空闲空间(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(+)
ORDER BY 1;
2、再查看temp文件位置
SELECT B.NAME TABLESPACE_NAME, A.NAME DATAFILE_NAME, ROUND(A.BYTES / (1024 * 1024), 2) "表空间大小"
FROM V$TEMPFILE A,V$TABLESPACE B
WHERE A.TS# = B.TS#
3、创建temp2 文件
CREATE TEMPORARY TABLESPACE TEMP02
TEMPFILE 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/TEMP02.DBF'
SIZE 1024M AUTOEXTEND ON NEXT 30720M MAXSIZE UNLIMITED;
-- size 是固定大小 autoextend on next 为最大扩展大小
4、指定TEMP文件默许文件
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;
5、查看是否有会话链接
SELECT A.USERNAME,A.SID,A.SERIAL#,A.SQL_ADDRESS,A.MACHINE,
A.PROGRAM,B.TABLESPACE,B.SEGTYPE,B.CONTENTS
FROM V$SESSION A,V$SORT_USAGE B
WHERE A.SADDR = B.SESSION_ADDR
6、删除原来的TEMP文件
DROP TABLESPACE TEMP including contents and datafiles cascade constraint
参考
http://blog.csdn.net/lmalds/article/details/33725657
转载于:https://blog.51cto.com/10788133/1970511