查看表空间
--查看表空间
SELECT a.tablespace_name AS "表空间名",
total AS "表空间大小",
free AS "表空间剩余大小",
(total - free) AS "表空间使用大小",
total / (1024 * 1024 * 1024) AS "表空间大小(G)",
free / (1024 * 1024 * 1024) AS "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) AS "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a
JOIN (SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
ON a. tablespace_name = b.tablespace_name
UNION ALL
--下面是临时表空间
SELECT a.tablespace_name AS "表空间名",
total AS "表空间大小",
free AS "表空间剩余大小",
(total - free) AS "表空间使用大小",
total / (1024 * 1024 * 1024) AS "表空间大小(G)",
free / (1024 * 1024 * 1024) AS "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) AS "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(BYTES_FREE) free
FROM V$TEMP_SPACE_HEADER
GROUP BY tablespace_name) a
JOIN (SELECT tablespace_name, SUM(bytes) total
FROM dba_temp_files
GROUP BY tablespace_name) b
ON a. tablespace_name = b.tablespace_name;
解决无法通过8 temp(UNDOTBS1)
加大 UNDO表空间即可
--查看UNDO为前缀的表空间
SELECT * FROM DBA_DATA_FILES WHERE tablespace_name like 'UNDO%';
--创建UNDO表空间
CREATE UNDO TABLESPACE UNDOTBS3 --表空间名称
DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS3.DBF' --文件位置
SIZE 4G --文件大小
AUTOEXTEND OFF --自动扩充 OFF关闭
;
--修改为自动扩充
ALTER DATABASE DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS3.DBF' --文件位置
AUTOEXTEND ON --自动扩充 OFF关闭
NEXT 500M MAXSIZE UNLIMITED --每次增加500M最大值为32G UNLIMITED可以改为大小K M G
;
--切换UNDO为UNDOTBS2
ALTER SYSTEM SET UNDO_TABLESPACE ='UNDOTBS3';
--删除原来的(也可以不删,不影响)
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
解决无法通过128 temp
添加或加大 TEMP表空间即可
--查看TEMP表空间
SELECT * FROM DBA_TEMP_FILES;
--添加与加大选一个就行
--添加 TEMP表空间 (添加数据文件)
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF' SIZE 4G;
--加大TEMP表空间(原数据文件)
ALTER DATABASE TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF' RESIZE 4G;
--修改为自动扩充
ALTER DATABASE TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF' AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
--如果TEMP 使用率到100%
ALTER TABLESPACE TEMP SHRINK SPACE; --收缩TEMP表空间