--查看表空间利用率 select a.tablespace_name, round(a.total_size) "total_size(MB)", round(a.total_size)-round(b.free_size,3) "used_size(MB)", round(b.free_size,3) "free_size(MB)", round(b.free_size/total_size*100,2)||'%' free_rate from ( select tablespace_name, sum(bytes)/1024/1024 total_size from dba_data_files group by tablespace_name ) a, ( select tablespace_name, sum(bytes)/1024/1024 free_size from dba_free_space group by tablespace_name ) b where a.tablespace_name = b.tablespace_name(+);
比如 TEMPSPACE这个表空间有TEMPFILE这个数据文件的话ALTER DATABASE DATAFILE TEMPFILE RESIZE 200M;
alter tablespace 表空间名 resize 大小
select a.tablespace_name, round(a.total_size) "total_size(MB)",
round(a.total_size)-round(b.free_size,3) "used_size(MB)",
round(b.free_size,3) "free_size(MB)", round(b.free_size/total_size*100,2)||'%' free_rate
from ( select tablespace_name, sum(bytes)/1024/1024 total_size
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes)/1024/1024 free_size
from dba_free_space
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name(+);
alter table tbname move 也可以实现
create tablespace tablespace_name
add datafile '....'的时候会指定一个大小,这个大小会跟随数据量的增长和表空间设为自动扩展而变大。一旦创建后不管里面有数据没大小都是不会变的,如果数据量增长了,表空间也会增长。