Oracle ASM 磁盘组常用 SQL【一、磁盘组整体容量与状态】-- 1.1 查看所有磁盘组核心指标最常用SELECTname,state,type,total_mb/1024 total_gb,free_mb/1024 free_gb,required_mirror_free_mb/1024 req_mir_gb,usable_file_mb/1024 usable_gb,decode(sign(usable_file_mb),1,充足,0,临界,-1,告警) status,round((total_mb-free_mb)/total_mb*100,2) used_pctFROM v$asm_diskgroup;-- 查看磁盘组统计信息正确列名SELECTname,state,type,total_mb,free_mb,usable_file_mbFROM v$asm_diskgroup_stat;-- 1.2 查看磁盘组详细参数SELECTgroup_number, name, state, type,sector_size, block_size,allocation_unit_size/1024/1024 au_mb,total_mb, free_mb, required_mirror_free_mb, usable_file_mb,offline_disks, voting_files,compatibility, database_compatibilityFROM v$asm_diskgroup;-- 1.3 查看磁盘组创建时间间接取最早加入的磁盘时间SELECTdg.name AS dg_name,MIN(d.create_date) AS earliest_disk_time,MAX(d.create_date) AS latest_disk_time,COUNT(*) AS disk_countFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberGROUP BY dg.name;【二、磁盘Disk信息】-- 2.1 查看所有磁盘基本信息SELECTdg.name diskgroup,d.name disk_name,d.path,d.failgroup,d.state,d.total_mb/1024 total_gb,d.free_mb/1024 free_gb,round(d.free_mb/d.total_mb*100,2) free_pct,d.mode_status,d.header_statusFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberORDER BY dg.name, d.failgroup, d.name;-- 2.2 查看离线/异常磁盘紧急排查SELECTdg.name diskgroup,d.name disk_name,d.path,d.failgroup,d.state,d.mode_status,d.header_status,d.mount_statusFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberWHERE d.state ! NORMALOR d.mode_status ! ONLINEOR d.mount_status ! CACHED;-- 2.3 按 FailGroup 汇总磁盘容量SELECTdg.name diskgroup,d.failgroup,COUNT(*) disk_count,SUM(d.total_mb)/1024 fg_total_gb,SUM(d.free_mb)/1024 fg_free_gb,round(SUM(d.free_mb)/SUM(d.total_mb)*100,2) fg_free_pctFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberWHERE d.state NORMALGROUP BY dg.name, d.failgroupORDER BY dg.name, fg_total_gb DESC;-- 2.4 查看磁盘读写统计性能排查SELECTname,reads,writes,read_errs,write_errs,read_time,write_timeFROM v$asm_disk_statWHERE read_errs 0 OR write_errs 0;-- 2.5如果需要查看磁盘头状态查磁盘视图SELECTname,path,failgroup,header_status,mount_status,mode_status,stateFROM v$asm_diskORDER BY failgroup, name;【三、文件File信息】-- 3.1 查看磁盘组内所有文件SELECTdg.name diskgroup,f.file_number,f.type file_type,f.bytes/1024/1024/1024 file_gb,f.redundancy,f.striped,f.creation_date,f.modification_dateFROM v$asm_file fJOIN v$asm_diskgroup dg ON f.group_number dg.group_numberORDER BY dg.name, f.bytes DESC;-- 3.2 按文件类型汇总空间占用SELECTdg.name diskgroup,f.type file_type,COUNT(*) file_count,SUM(f.bytes)/1024/1024/1024 total_gb,AVG(f.bytes)/1024/1024/1024 avg_gbFROM v$asm_file fJOIN v$asm_diskgroup dg ON f.group_number dg.group_numberGROUP BY dg.name, f.typeORDER BY dg.name, total_gb DESC;-- 3.3 查看数据文件与 ASM 文件的映射关系ASM 实例通过别名和文件类型查数据文件SELECTa.name asm_path,f.file_number,f.bytes/1024/1024/1024 gb,f.redundancyFROM v$asm_alias aJOIN v$asm_file f ON a.group_number f.group_number AND a.file_number f.file_numberWHERE f.type DATAFILEORDER BY f.bytes DESC;-- 3.4 查看大文件SELECTdg.name diskgroup,f.file_number,f.type,f.bytes/1024/1024/1024 gb,f.redundancyFROM v$asm_file fJOIN v$asm_diskgroup dg ON f.group_number dg.group_numberWHERE f.bytes 10737418240 -- 10GBORDER BY f.bytes DESC;【四、别名Alias与目录】-- 4.1 查看所有别名正确列名SELECTdg.name diskgroup,a.name alias_name,a.file_number,a.alias_directory,a.system_created,a.parent_index,a.reference_indexFROM v$asm_alias aJOIN v$asm_diskgroup dg ON a.group_number dg.group_numberORDER BY dg.name, a.name;-- 4.2 查看文件别名 文件类型通过 v$asm_file 关联SELECTdg.name diskgroup,a.name alias_path,f.type file_type,f.bytes/1024/1024/1024 gbFROM v$asm_alias aJOIN v$asm_file f ON a.group_number f.group_number AND a.file_number f.file_numberWHERE a.alias_directory NORDER BY f.bytes DESC;-- 4.3 只查看目录结构SELECTdg.name diskgroup,a.name dir_name,a.parent_index,a.reference_index,a.system_createdFROM v$asm_alias aJOIN v$asm_diskgroup dg ON a.group_number dg.group_numberWHERE a.alias_directory YORDER BY dg.name, a.name;【五、重平衡Rebalance监控】-- 6.1 查看当前重平衡操作SELECTdg.name diskgroup,r.operation,r.state,r.power,r.actual,r.sofar,r.est_work,r.est_rate,r.est_minutes,r.error_codeFROM v$asm_operation rJOIN v$asm_diskgroup dg ON r.group_number dg.group_number;-- 6.2 查看重平衡进度百分比SELECTgroup_number,operation,state,round(sofar/est_work*100,2) pct_complete,est_minutes || min remainingFROM v$asm_operationWHERE state RUN;【七、模板与属性】-- 7.1 查看磁盘组属性SELECTdg.name diskgroup,a.name attr_name,a.value attr_value,a.read_only,a.system_createdFROM v$asm_attribute aJOIN v$asm_diskgroup dg ON a.group_number dg.group_numberORDER BY dg.name, a.name;-- 7.2 查看文件模板SELECTdg.name diskgroup,t.name template_name,t.redundancy,t.stripe,t.systemFROM v$asm_template tJOIN v$asm_diskgroup dg ON t.group_number dg.group_numberORDER BY dg.name, t.name;【八、I/O 与性能】-- 8.1 磁盘组 I/O 统计通过 v$asm_disk_stat 聚合SELECTdg.name diskgroup,SUM(ds.reads) reads,SUM(ds.writes) writes,SUM(ds.read_errs) read_errs,SUM(ds.write_errs) write_errs,SUM(ds.read_time) read_time,SUM(ds.write_time) write_time,SUM(ds.bytes_read)/1024/1024/1024 read_gb,SUM(ds.bytes_written)/1024/1024/1024 write_gbFROM v$asm_diskgroup_stat dgJOIN v$asm_disk_stat ds ON dg.group_number ds.group_numberGROUP BY dg.name;-- 8.2 磁盘 I/O 性能找慢盘SELECTdg.name diskgroup,ds.name disk_name,ds.path,ds.reads,ds.writes,ds.read_errs,ds.write_errs,ds.read_time,ds.write_time,ds.bytes_read/1024/1024/1024 read_gb,ds.bytes_written/1024/1024/1024 write_gbFROM v$asm_disk_stat dsJOIN v$asm_diskgroup dg ON ds.group_number dg.group_numberWHERE ds.state NORMAL;【九、ASM 管理操作 SQL】-- 10.1 修改重平衡功率在线调整ALTER DISKGROUP DATADG REBALANCE POWER 4;-- 10.2 检查磁盘组一致性ALTER DISKGROUP DATADG CHECK ALL;常用视图速查v$asm_diskgroup → 磁盘组信息v$asm_disk → 磁盘信息v$asm_disk_stat → 磁盘 I/O 统计v$asm_file → 文件信息v$asm_alias → 别名/目录信息v$asm_client → 客户端连接信息v$asm_operation → 重平衡操作v$asm_template → 文件模板v$asm_attribute → 磁盘组属性v$asm_diskgroup_stat → 磁盘组统计
ASM常用相关sql
Oracle ASM 磁盘组常用 SQL【一、磁盘组整体容量与状态】-- 1.1 查看所有磁盘组核心指标最常用SELECTname,state,type,total_mb/1024 total_gb,free_mb/1024 free_gb,required_mirror_free_mb/1024 req_mir_gb,usable_file_mb/1024 usable_gb,decode(sign(usable_file_mb),1,充足,0,临界,-1,告警) status,round((total_mb-free_mb)/total_mb*100,2) used_pctFROM v$asm_diskgroup;-- 查看磁盘组统计信息正确列名SELECTname,state,type,total_mb,free_mb,usable_file_mbFROM v$asm_diskgroup_stat;-- 1.2 查看磁盘组详细参数SELECTgroup_number, name, state, type,sector_size, block_size,allocation_unit_size/1024/1024 au_mb,total_mb, free_mb, required_mirror_free_mb, usable_file_mb,offline_disks, voting_files,compatibility, database_compatibilityFROM v$asm_diskgroup;-- 1.3 查看磁盘组创建时间间接取最早加入的磁盘时间SELECTdg.name AS dg_name,MIN(d.create_date) AS earliest_disk_time,MAX(d.create_date) AS latest_disk_time,COUNT(*) AS disk_countFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberGROUP BY dg.name;【二、磁盘Disk信息】-- 2.1 查看所有磁盘基本信息SELECTdg.name diskgroup,d.name disk_name,d.path,d.failgroup,d.state,d.total_mb/1024 total_gb,d.free_mb/1024 free_gb,round(d.free_mb/d.total_mb*100,2) free_pct,d.mode_status,d.header_statusFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberORDER BY dg.name, d.failgroup, d.name;-- 2.2 查看离线/异常磁盘紧急排查SELECTdg.name diskgroup,d.name disk_name,d.path,d.failgroup,d.state,d.mode_status,d.header_status,d.mount_statusFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberWHERE d.state ! NORMALOR d.mode_status ! ONLINEOR d.mount_status ! CACHED;-- 2.3 按 FailGroup 汇总磁盘容量SELECTdg.name diskgroup,d.failgroup,COUNT(*) disk_count,SUM(d.total_mb)/1024 fg_total_gb,SUM(d.free_mb)/1024 fg_free_gb,round(SUM(d.free_mb)/SUM(d.total_mb)*100,2) fg_free_pctFROM v$asm_disk dJOIN v$asm_diskgroup dg ON d.group_number dg.group_numberWHERE d.state NORMALGROUP BY dg.name, d.failgroupORDER BY dg.name, fg_total_gb DESC;-- 2.4 查看磁盘读写统计性能排查SELECTname,reads,writes,read_errs,write_errs,read_time,write_timeFROM v$asm_disk_statWHERE read_errs 0 OR write_errs 0;-- 2.5如果需要查看磁盘头状态查磁盘视图SELECTname,path,failgroup,header_status,mount_status,mode_status,stateFROM v$asm_diskORDER BY failgroup, name;【三、文件File信息】-- 3.1 查看磁盘组内所有文件SELECTdg.name diskgroup,f.file_number,f.type file_type,f.bytes/1024/1024/1024 file_gb,f.redundancy,f.striped,f.creation_date,f.modification_dateFROM v$asm_file fJOIN v$asm_diskgroup dg ON f.group_number dg.group_numberORDER BY dg.name, f.bytes DESC;-- 3.2 按文件类型汇总空间占用SELECTdg.name diskgroup,f.type file_type,COUNT(*) file_count,SUM(f.bytes)/1024/1024/1024 total_gb,AVG(f.bytes)/1024/1024/1024 avg_gbFROM v$asm_file fJOIN v$asm_diskgroup dg ON f.group_number dg.group_numberGROUP BY dg.name, f.typeORDER BY dg.name, total_gb DESC;-- 3.3 查看数据文件与 ASM 文件的映射关系ASM 实例通过别名和文件类型查数据文件SELECTa.name asm_path,f.file_number,f.bytes/1024/1024/1024 gb,f.redundancyFROM v$asm_alias aJOIN v$asm_file f ON a.group_number f.group_number AND a.file_number f.file_numberWHERE f.type DATAFILEORDER BY f.bytes DESC;-- 3.4 查看大文件SELECTdg.name diskgroup,f.file_number,f.type,f.bytes/1024/1024/1024 gb,f.redundancyFROM v$asm_file fJOIN v$asm_diskgroup dg ON f.group_number dg.group_numberWHERE f.bytes 10737418240 -- 10GBORDER BY f.bytes DESC;【四、别名Alias与目录】-- 4.1 查看所有别名正确列名SELECTdg.name diskgroup,a.name alias_name,a.file_number,a.alias_directory,a.system_created,a.parent_index,a.reference_indexFROM v$asm_alias aJOIN v$asm_diskgroup dg ON a.group_number dg.group_numberORDER BY dg.name, a.name;-- 4.2 查看文件别名 文件类型通过 v$asm_file 关联SELECTdg.name diskgroup,a.name alias_path,f.type file_type,f.bytes/1024/1024/1024 gbFROM v$asm_alias aJOIN v$asm_file f ON a.group_number f.group_number AND a.file_number f.file_numberWHERE a.alias_directory NORDER BY f.bytes DESC;-- 4.3 只查看目录结构SELECTdg.name diskgroup,a.name dir_name,a.parent_index,a.reference_index,a.system_createdFROM v$asm_alias aJOIN v$asm_diskgroup dg ON a.group_number dg.group_numberWHERE a.alias_directory YORDER BY dg.name, a.name;【五、重平衡Rebalance监控】-- 6.1 查看当前重平衡操作SELECTdg.name diskgroup,r.operation,r.state,r.power,r.actual,r.sofar,r.est_work,r.est_rate,r.est_minutes,r.error_codeFROM v$asm_operation rJOIN v$asm_diskgroup dg ON r.group_number dg.group_number;-- 6.2 查看重平衡进度百分比SELECTgroup_number,operation,state,round(sofar/est_work*100,2) pct_complete,est_minutes || min remainingFROM v$asm_operationWHERE state RUN;【七、模板与属性】-- 7.1 查看磁盘组属性SELECTdg.name diskgroup,a.name attr_name,a.value attr_value,a.read_only,a.system_createdFROM v$asm_attribute aJOIN v$asm_diskgroup dg ON a.group_number dg.group_numberORDER BY dg.name, a.name;-- 7.2 查看文件模板SELECTdg.name diskgroup,t.name template_name,t.redundancy,t.stripe,t.systemFROM v$asm_template tJOIN v$asm_diskgroup dg ON t.group_number dg.group_numberORDER BY dg.name, t.name;【八、I/O 与性能】-- 8.1 磁盘组 I/O 统计通过 v$asm_disk_stat 聚合SELECTdg.name diskgroup,SUM(ds.reads) reads,SUM(ds.writes) writes,SUM(ds.read_errs) read_errs,SUM(ds.write_errs) write_errs,SUM(ds.read_time) read_time,SUM(ds.write_time) write_time,SUM(ds.bytes_read)/1024/1024/1024 read_gb,SUM(ds.bytes_written)/1024/1024/1024 write_gbFROM v$asm_diskgroup_stat dgJOIN v$asm_disk_stat ds ON dg.group_number ds.group_numberGROUP BY dg.name;-- 8.2 磁盘 I/O 性能找慢盘SELECTdg.name diskgroup,ds.name disk_name,ds.path,ds.reads,ds.writes,ds.read_errs,ds.write_errs,ds.read_time,ds.write_time,ds.bytes_read/1024/1024/1024 read_gb,ds.bytes_written/1024/1024/1024 write_gbFROM v$asm_disk_stat dsJOIN v$asm_diskgroup dg ON ds.group_number dg.group_numberWHERE ds.state NORMAL;【九、ASM 管理操作 SQL】-- 10.1 修改重平衡功率在线调整ALTER DISKGROUP DATADG REBALANCE POWER 4;-- 10.2 检查磁盘组一致性ALTER DISKGROUP DATADG CHECK ALL;常用视图速查v$asm_diskgroup → 磁盘组信息v$asm_disk → 磁盘信息v$asm_disk_stat → 磁盘 I/O 统计v$asm_file → 文件信息v$asm_alias → 别名/目录信息v$asm_client → 客户端连接信息v$asm_operation → 重平衡操作v$asm_template → 文件模板v$asm_attribute → 磁盘组属性v$asm_diskgroup_stat → 磁盘组统计
