查询表空间目录及是否自动扩展
select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;
总的表空间情况
select substr(a.tablespace_name,1,15) tablespacename,sum(a.bytes/1024/1024) as "totle_size(M)",
sum(nvl(b.free_space1/1024/1024,0)) as "Free_space(M)",
sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0)) as "Used_space(M)",
round((sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0)))*100/sum(a.bytes/1024/1024),2) as "Used_percent%" from dba_data_files a,
(select sum(nvl(bytes,0)) free_space1,file_id from dba_free_space group by file_id) b
where a.file_id = b.file_id(+)
group by a.tablespace_name
order by "Used_percent%" desc
扩充表空间
alter tablespace 表空间名称 add datafile '/usr2/prog/oracle/product/10.2.0.1/oradata/ora11g/sdtelecom04.dbf' size 1G autoextend off;
