查看Oracle表空间使用率

1、查看表空间使用率包含自动扩展部分 set pages 200 set line 200 set wrap off col tablespace_name for a20  col alloc_gb for a10  col used_gb for a10 col used_gb for a10 col max_gb for a10 col max_free_g for a10 col used_of_max_pct for a15 select a.tablespace_name, to_char(round(a.bytes_alloc / power(2,30),4),'fm9999990.0000') alloc_gb, to_char(round((a.bytes_alloc - nvl(b.bytes_free,0))/power(2,30) , 4) , 'fm9999990.0000') used_gb, to_char(round(a.maxbytes / power(2,30),4),'fm9999990.0000') max_gb, to_char(round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,0))/power(2,30),4),'fm9999990.0000') max_free_g, to_char(round((a.bytes_alloc-nvl(b.bytes_free,0))*100/a.maxbytes ,2) ,'fm9999990.0000')used_of_max_pct, to_char(round((a.bytes_alloc-nvl(b.bytes_free,0))*100/a.bytes_alloc ,2) ,'fm9999990.0000')used_of_alloc_pct, to_char(round((a.bytes_alloc)*100/a.maxbytes ,2) ,'fm9999990.0000')alloc_of_max_pct, ceil((a.maxbytes-a.bytes_alloc)/power(2,30)) osfs_need from  (select f.tablespace_name,sum(f.bytes) bytes_alloc,sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name,sum(f.bytes) bytes_free from dba_free_space f group by tablespace_name) b where a.tablespace_name = b.tablespace_name(+) order by round((a.bytes_alloc-nvl(b.bytes_free,0))*100/a.maxbytes,2) desc; 2、查看asm磁盘组使用率 set pages 200 line 200 wrap offselect name,type,to_char(round(total_mb/1024,4),'fm9999990.0000') total_gb,to_char(round(free_mb/1024,4),'fm9999990.0000') free_gb,to_char(round(cold_used_mb/1024,4),'fm9999990.0000') used_gb,to_char(round(free_mb/total_mb*100,2),'fm9999990.0000') free_pct,to_char(round(cold_used_mb/total_mb*100,2),'fm9999990.0000') used_pctfrom v$asm_diskgroup; 3、表空间扩容 表空间扩瞳 col file_name for a50 select file_name,bytes/1024/1024/1024,maxbytes/1024/1024/1024,autoextensible,tablespace_name  from dba_data_files where tablespace_name='FT_TBS' order by file_name; alter tablespace DEFECT_TBS_DATA_03 add datafile '+DATA2/EDA/DATAFILE/defect_tbs_data_04.dbf' size 1024M  autoextend on; 使用DBA_TABLESPACE_USAGE_METRICS ，统计信息不及时时不准 SELECT tablespace_name, to_char(round(used_space/1024/1024*8,2),'fm9999990.0000') as "USED_GB", to_char(round((tablespace_size - used_space)/1024/1024*8,2),'fm9999990.0000') as "FREE_GB", --to_char(round(tablespace_size/1024/1024*8,2),'fm9999990.0000') as "MAX_GB", to_char(round(used_percent,2),'fm9999990.0000') as "USED_OF_MAX_PCT" FROM DBA_TABLESPACE_USAGE_METRICS  order by round(used_percent,2) desc;