oracle表空间相关sql

前言

上个月，一个刚负责维护不久的对外项目，前台直接就提交不了了，排查发现是oracle数据库表空间不足了。数据库是第三方提供的，但是由我们自己维护，交接有点问题，导致事情的发生，引以为戒！

正文

概念

在总结查看表空间状态语句前，先来看看两个概念：

• 表空间
  表空间是指一个或多个数据文件的逻辑集合,归属于用户
• 数据文件
  实际存放数据的文件

一个表空间至少包含一个数据文件，一个数据文件只能属于一个表空间。
表空间满了，一般就是指它包含数据文件空间不够用了。

分析

数据文件一般会指定一个初始大小，比如给定100M的文件大小。假设表空间现只有一个数据文件，初始大小100M，数据都写到这100M里面， 一般空间使用完了会让它自动去扩展这个数据文件的大小，例如每次扩展20M大小，避免数据无法写入。

问题来了

oracle默认的数据块大小是8k，而Oracle的物理文件最大只允许4194304个数据块(这个参数具体由操作系统决定，一般应该是此数字)， 单文件最大值4194304×8/1024/1024GB即32GB。
当数据文件达到了32GB这个临界值，面临的就是数据无法写入的问题了，常见解决办法：

• 增加数据文件的个数（推荐）
  一个表空间可以有多个数据文件，所以再增加数据文件来对数据进行存储支持。注意增加数据文件，配置好初始大小、自动扩展及自动扩展的大小。
• 设置数据块的大小
  数据库的表空间的块大小建立好之后就不能修改，只能新建一个表空间，或者新建数据库，然后将数据导入到新的数据库。所以只适合创建表空间的时候指定数据块大小。

sql语句

下面给总结了一下查询表空间状态信息的语句。方便以后使用：

查看当前用户所在得表空间（后续sql表名称以TS_NAME代替，使用时注意替换）

select username,default_tablespace 表空间名称 from user_users;

汇总的sql,一般这个就能看出所有需要的信息啦

--查看数据文件信息(不保留小数)  增量（oracle默认1块大小8k）
 select TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE 是否自动拓展, 
 round(bytes / (1024 * 1024), 0) 所占大小M,  round((MAXBYTES/1024/1024),0) 最大限制大小M,
 INCREMENT_BY*8/1024 自动拓展大小M 
 from dba_data_files where tablespace_name='TS_NAME';

查看数据文件使用情况

select 　　
b.file_name 物理文件名,
b.tablespace_name 表空间,
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 　
and b.tablespace_name='TS_NAME'　
group by b.tablespace_name, b.file_name, b.bytes 　　
order by b.tablespace_name;

查看增量1块多大，打开命令行（默认块大小为8192字节。即8k）

 show parameter db_block_size

增加数据文件，初始大小100M，自动扩容。当表空间满了使用

ALTER TABLESPACE TS_NAME ADD DATAFILE
'+DATA/sgbgt/datafile/TS_NAME_20191205' SIZE 1000M
AUTOEXTEND ON NEXT 100 MAXSIZE UNLIMITED;

修改对应数据文件自动增长每次增长20m(根据实际需求设置，可设置大一点)。

 alter database datafile 'd:\sq01.bdf' autoextend on next 20m  ; 

最后

所有资料来自互联网，非DBA，如有错误请帮忙指正，谢谢！
希望自己备用的前提下，还能帮助到别人，看到这个也能大概明白是什么、为什么和怎么做！
参考文章：
「oracle」为什么单个datafile最大32G？
Oracle 查看和扩展表空间
Oracle表空间（tablespaces）
ORA-01691：Lob 段无法通过8192（在表空间XXX中）扩展的解决方案
探秘Oracle表空间、用户、表之间的关系