oracle 表空间扩展与查询

如果出现表空间不足的情况，可以使用下面语句进行扩展
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