1) 以m为单位的剩余表空间

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS "FREE SPACE(M)"   FROM DBA_FREE_SPACE  GROUP BY TABLESPACE_NAME;

2) 查看各表空间数据文件及大小

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 as datafile_size FROM DBA_DATA_FILES;

3) 统计表空间大小（数据文件大小相加）

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 as total FROM DBA_DATA_FILES group by tablespace_name;

4) 已使用的表空间大小

select a.tablespace_name,total,free,total-free used from ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a, ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name;

5) 查看表空间是否自增

select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id;

6) 为空间不足的表空间增加数据文件

ALTER TABLESPACE &tablespace_name ADD DATAFILE '/oradata/users01.dbf' SIZE 2G;

 

持续更新中...