oracle查看表扩展,oracle查询及扩充表空间

查询表空间目录及是否自动扩展

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;