【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;