【DBA笔记3】Oracle之表空间使用率满及空间不足解决方案(上)
目录
问题背景:
有项目上出现前台业务功能报错,怀疑是数据库GS表空间满
ORA-01653:unable to extend table GSP### by 7 in tablespace GS
问题描述:
错误的具体内容如下:
ORA-01653: unable to extend table GSP### by 7 in tablespace GS
翻译:GSP###表在GS表空间不能扩展,应该是表空间不足导致。
原因:
一:表空间的自动扩展功能没有开;
二:表空间自动扩展开了,但是数据文件已自动扩展到上限,最大32G
三:服务器磁盘空间不够用了,数据文件所在的磁盘目录下没有空间了
解决方案:
先查看Oracle数据库表空间大小,然后看一下表空间下的所有的数据文件的自动扩展功能是否打开;若已经打开了看是不是达到了自动扩展的上限,若上限了需要增加数据文件,若没上限那么我们就需要扩大表空间。
操作步骤:
一、查询对应表空间使用情况
查询表空间使用情况,该sql无需修改
SELECT a.tablespace_name "表空间名",
a.bytes / 1024 / 1024 "表空间大小(M)",
(a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",
b.bytes / 1024 / 1024 "空闲空间(M)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
FROM (SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC
发现GSP## 表空间使用比为99.99%(>90%),继续查看该表空间有几个数据文件,该sql需要改tablespace_name
SELECT file_name,
tablespace_name,
bytes / 1024 / 1024 "bytes MB",
maxbytes / 1024 / 1024 "maxbytes MB"
FROM dba_data_files
WHERE tablespace_name = '表空间名称';
继续查询是否是自动扩展,改tablespace_name
SELECT file_id, file_name, tablespace_name, autoextensible, increment_by
FROM dba_data_files
WHERE tablespace_name = '表空间名称'
ORDER BY file_id desc;
autoextensible"列对应的值是YES还是NO,若是NO,说明该表空间的自动扩展功能没有开,改成YES就可以了。
二、修改对应表空间
之后有两种改法:一种是增大数据文件大小,一种是增加数据文件数量。
第一种方法:增大数据文件大小
首先找出该表空间对应的数据文件及全路径,该路径对应FILE_NAME字段。
SELECT * FROM dba_data_files t WHERE t.tablespace_name='表空间名称';
修改文件大小
alter database datafile '全路径的数据文件名称' resize ***M;
第二种方法:增加数据文件数量
首先要确认再增加一个数据文件,磁盘空间是否足够
【用操作系统UNIX、Linux中的df -g命令(查看下可以使用的磁盘空间大小)】
新增一个数据文件,全路径的数据文件名称为该新增数据文件的全路径文件名称。大小为***M,自动扩展功能打开,且该数据文件的最大扩展值为20G。
alter tablespace 表空间名称 add datafile '全路径的数据文件名称' size ***M
autoextend on maxsize 20G;
验证已经增加的数据文件,现在应看到GS表空间下有新建的数据文件,且GS表空间使用占比降了下来了。
SELECT file_name, file_id, tablespace_name
FROM dba_data_files
WHERE tablespace_name = '表空间名称'
到这里问题应该已经解决,再回头去验证前端功能就可以了。
PS:分享一些自己总结的关于表空间比较实用的几个sql,下节分享下通过job任务自动增加数据文件的方案
--查看现所有表空间使用率(包含临时表空间)
select * from (
Select a.tablespace_name,
(a.bytes- b.bytes) "表空间使用大小(BYTE)",
a.bytes/(1024*1024*1024) "表空间大小(GB)",
b.bytes/(1024*1024*1024) "表空间剩余大小(GB)",
(a.bytes- b.bytes)/(1024*1024*1024) "表空间使用大小(GB)",
to_char((1 - b.bytes/a.bytes)*100,'99.99999') || '%' "使用率"
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
d.bytes_used "表空间使用大小(BYTE)",
c.bytes/(1024*1024*1024) "表空间大小(GB)",
(c.bytes-d.bytes_used)/(1024*1024*1024) "表空间剩余大小(GB)",
d.bytes_used/(1024*1024*1024) "表空间使用大小(GB)",
to_char(d.bytes_used*100/c.bytes,'99.99999') || '%' "使用率"
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name;
--查询现所有表空间文件存放目录,file_name 就是文件存放路径
select b.file_id,b.file_name 数据文件路径,
b.tablespace_name 表空间名称,
b.autoextensible,
b.bytes / 1024 / 1024 大小M,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.file_id,b.tablespace_name, b.file_name, b.bytes,b.autoextensible
order by b.file_id;
--单纯删除表空间
drop tablespace 表空间名称;
--删除表空间及内容(表空间名称要大写)
drop tablespace "表空间名称" including contents;
--完全删除(表空间 + 数据文件)
drop tablespace "表空间名称" including contents and datafiles cascade constraints;
--创建表空间
create tablespace 表空间名称
logging
datafile '数据文件路径'
size 25000m
autoextend on
next 100m maxsize 30000m
extent management local;
--更改表空间数据文件大小及设置自动扩展
alter database datafile 原数据文件路径 autoextend on;
alter database datafile 原数据文件路径 resize 1024M;
--为表空间增加新的数据文件并设置自动扩展(表空间满32G后不能再自动扩展)
alter tablespace '表空间名称' add datafile '新数据文件路径' size 1000m autoextend on next 100m;
--查看当前数据库默认临时表空间:
select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
--获取当时创建某一表空间的语句
SELECT dbms_metadata.get_ddl('TABLESPACE', '表空间名称') FROM dual;
--为TEMP临时表空间增加新的数据文件并设置自动扩展
alter tablespace TEMP add tempfile '/****' size 1000m autoextend on next 100m;