表空间管理的相关的一些sql-优快云博客

本文链接：https://blog.youkuaiyun.com/jrunner/article/details/4044380

本文介绍了一种检查Oracle数据库表空间使用情况的方法，并提供了查询表空间使用率、数据文件大小及扩展性的SQL语句。此外，还介绍了如何通过SQL语句添加和删除数据文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

那天系统出现问题，同事说会不会是数据库空间不足造成的，想了想查看空间使用方面的sql我根本不知道，呵呵，当时就在baidu随便搜了个，运行很慢，当时也没在意就过去了，今天碰巧看到itpub上一位兄弟总结了这方面的操作，我利用工作时间学习实践了一下呵呵，然后又用自己的语言稍加了修改，姑且就算原创吧，呵呵。

1. 查看当前各个表空间的使用情况

select tablespace_name,
       sum_MB,
       free_MB,
       used_MB,
       used_persent
from (select t.tablespace_name,
               t.sum_MB,
               f.free_MB,
               t.sum_MB - f.free_MB used_MB,
               round(((t.sum_MB - f.free_MB) / t.sum_MB), 4) * 100 || '%' used_persent
          from (select a.tablespace_name, sum(a.bytes) / 1024 / 1024 sum_MB
                  from dba_data_files a
                 group by a.tablespace_name) t, --总量view
               (select b.tablespace_name, sum(b.bytes) / 1024 / 1024 free_MB
                  from dba_free_space b
                 group by b.tablespace_name) f --剩余量view
         where t.tablespace_name = f.tablespace_name)
order by 5 desc

2. 当发现used_persent比较高的表空间，查询这个表空间的各个数据文件的大小，最大值以及是否能够自动扩展来判断是否需要对其手动添加数据文件

select a.bytes / 1024 / 1024 current_mb, -- 当前大小
          a.maxbytes / 1024/1024 max_mb,   --允许最大值
          a.autoextensible                    --是否可以自动扩展
from dba_data_files a
where a.tablespace_name = 'ILEARN_DATA'

3. 得知需要添加数据文件之前，要查看磁盘使用情况
运行
    df -h       #-h 人类可读的方式
查看硬盘使用情况，情况良好则可以添加数据文件

4. 为了书写方便，首先获取要表空间建立的ddl语句

select dbms_metadata.get_ddl('TABLESPACE', 'YC_TBS') from dual

--eg
CREATE TABLESPACE "YC_TBS" DATAFILE
'/home/oracle/oracle/oradata/demoyc/yc.dbf' SIZE 104857600,
'/home/oracle/oracle/oradata/demoyc/yc02.dbf' SIZE 20971520,
'/home/oracle/oracle/oradata/demoyc/yc03.dbf' SIZE 20971520

AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

注意：yc_tbs表空间包含3个数据文件，其中yc.dbf和yc02.dbf是不可以自动扩展的，yc03.dbf是可以自动扩展的，由此可知自动扩展autoextend是datafile的属性，即从dba_data_files表中可以查询得知数据文件是否可以自动扩展

5. 添加数据文件
alter tablespace yc_tbs
add datafile '/home/oracle/oracle/oradata/demoyc/yc03.dbf'
SIZE 20M
autoextend on --允许自动扩展

6. 如果建立错误可以删除这个数据文件
alter tablespace yc_tbs
drop datafile '/home/oracle/oracle/oradata/demoyc/yc02.dbf'

7. 验证已经增加的数据文件
select * from dba_data_files where tablespace_name = 'YC_TBS'