我要评论有些小白在操作数据库时随便新建表空间，并且喜欢直接设置最大值把表空间文件直接设置成32g，而后在导库的时候又没进行正确操作导致新建的表空间并未使用，而把数据又全部导入users表空间了，这样就造成数据库服务器磁盘空间的浪费，对于这种情况有两种解决方案，把表空间减小或者直接删除。
首先附一个查询表空间使用率的sql吧（需要有dba权限的用户）：
select a.tablespace_name,
total,
free,
total - free as used,
substr(free / total * 100, 1, 5) as "free%",
substr((total - free) / total * 100, 1, 5) as "used%"
from (select tablespace_name, sum(bytes) / 1024 / 1024 as total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;
此条sql可以非常直观的看到数据库中各个表空间的实时使用率情况，在数据库导库的时候也可以定时刷新进行查看（但不包括临时表空间）。
一、修改表空间数据文件大小
1、查询表空间数据文件
select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name
2、找到表空间对应的数据文件后对数据文件的大小进行调整
alter database datafile '/oracle/oradata/ora16/data01.dbf' resize 100m --注意替换表空间路径
在修改表空间大小的时候有可能会报错，报错信息**“ora-03297: 文件包含在请求的 resize 值以外使用的数据”**，由于表空间中数据并非按顺序分布，因此在删减掉的表空间中可能也存在数据，就会有面的报错信息，这个问题的解决方案我以后会在博客中更新；
临时的替代解决方案就是将表空间大小的数值稍微改大点重试，比如由10g修改为100m时报错，可以先修改为500m，如果不报错就再减小…
二、删除数据库表空间及文件
删除表空间及数据文件的sql：
drop tablespace xxx including contents and datafiles;
删除表空间之前记得断开所有用户连接，否则无法删除，会报错。
删除表空间之后在数据库服务器上使用df -lh(linux)/df -g(aix)上查看磁盘空间容量发现和删除前的是一样的，并没有变化。
用lsof -n | grep deleted找到刚才删除的dbf文件（后边会有一个deleted的标识），查看对应的pid，直接kill掉就可以了。
这是因为这些被删除的表空间数据文件还被数据库进程所持有，在进程关闭之前磁盘空间是不会释放的，如果想要验证该方法的可用性把数据库重启一次就可以了，数据库重启的效果等同于使用kill命令。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论