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 off

select 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_pct

from 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;