tablespace空间管理

查看表空间的使用情况
select a.tablespace_name ,a.bytes bytes_used,b.largest,round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes)/a.bytes) desc;

移动数据文件
一种方法
1. alter tablespace dgyy offline;
2. mv 'oldplacefile' 'newpacefile'
3.alter tablespace userdate rename
datafile 'oldplacefile1'
to 'newpacefile';
第二种
1. database must shutdown
2. mv 'oldplacefile' 'newpacefile'
3. startup mount
4. alter database rename file 'oldfile' to 'newfile'
5. alter database open

表空间扩容
alter tablespace dgyy add datafile
'/u01/newfile.dbf'
size 40m
autoextend on next 10m
maxsize 1000M;

alter database datafile '/u01/file.dbf'
resize 1000M;

alter database datafile '/u01/file.dbf'
autoextend on next 10m
maxsize 10000m;