set pagesize 1000;col tablespace_name for a14;select d.tablespace_name,space "sum_space(m)",blocks sum_blocks,space-nvl(free_space,0) "used_space(m)",round((1-nvl(free_space,0)/space)*100,2) "used_rate(%)",free_space "free_space(m)"from(select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocksfrom dba_data_filesgroup by tablespace_name) d,(select tablespace_name,round(sum(bytes)/(1024*1024),2) free_spacefrom dba_free_spacegroup by tablespace_name) fwhere d.tablespace_name = f.tablespace_name(+) union allselect d.tablespace_name,space "sum_space(m)",blocks sum_blocks,used_space "used_space(m)",round(nvl(used_space,0)/space*100,2) "used_rate(%)",nvl(free_space,0) "free_space(m)"from(select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocksfrom dba_temp_filesgroup by tablespace_name) d,(select tablespace_name,round(sum(bytes_used)/(1024*1024),2) used_space,round(sum(bytes_free)/(1024*1024),2) free_spacefrom v$temp_space_headergroup by tablespace_name) fwhere d.tablespace_name = f.tablespace_name(+);

通过上面的SQL查到表空间的使用情况，通过以下SQL查找是否表空间自动扩展。

SQL>  select  STATUS,AUTOEXTENSIBLE from  dba_data_files  where tablespace_name='ISS_UPDATE';

STATUS    AUT

--------- ---

AVAILABLE NO

AVAILABLE NO

AVAILABLE NO

AVAILABLE NO

AVAILABLE NO

AVAILABLE NO

AVAILABLE NO