如果出现表空间不足的情况,可以使用下面语句进行扩展
1 查看表空间总大小、使用率、剩余空间
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
2 查看表空间文件
SELECT * FROM DBA_DATA_FILES
3 修改表空间自动增长
alter database datafile 'xxxxx.DBF' autoextend on; //自动增长
ALTER DATABASE DATAFILE 'xxx\xxx.dbf' AUTOEXTEND ON NEXT 200M ;//每次自动增长200m
ALTER DATABASE DATAFILE 'xxx\xxx.dbf' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;//每次自动增长200m,表空间最大1G
4 扩展所需表空间大小
alter database datafile 'xxx.DBF' resize 1024m;
5 增加数据文件个数
alter tablespace 表空间名称 add datafile 'new_xxx.DBF' size 500m