Oracle 表空间

1、查看表空间总大小、使用率、剩余空间

select a.tablespace_name, total, free, total-free as used, cast((free/total * 100) as number(10,2)) as "FREE%", cast(((total - free)/total * 100) as number(10,2)) 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

image.png

2、查看表空间数据文件

select file_name,round(bytes/(1024*1024),0) from dba_data_files where tablespace_name = 'your_tablespace_name';

image.png

3、给表空间添加数据文件

alter tablespace your_tablespace_name add datafile '/***/tablespacefile/itm02.dbf' size 5170M;

补充：如果表空间使用率已经达到100%，将数据写入到分类到该表空间的表的时候，就会报错。

ERROR [com.itl.iap.common.base.aop.JdbcConfig] JdbcConfig.java:307 - 保存***异常
java.sql.BatchUpdateException: ORA-01653: 表 DATABASE.TABLE_NAME 无法通过 8192 (在表空间 YOUR_TABLE_SPACE 中) 扩展