Oracle Undo statistics  ..

-- single instance 
set lines 1000 pages 200
column inst_id head inst#         format 99
column hour    head "hour|(dd-mm-yyyy-hh24)"                         justify centre
column "activesize"          head "active|size(mb)"    format 99,999 justify right
column undosize   head "undo|size(mb)"    format 99,999 justify right
column expiredsize   head "expired|size(mb)"   format 99,999 justify right
column unexpiredsize          head "unexpired|size(mb)"   format 99,999 justify right
column expiredrelsize          head "expired rel|size(mb)"          format 99,999 justify right
column maxconcurrency          head "max |concurrent|txn(#)"          format 99,999 justify right
column txncount   head "total|txn (#)"    format 99,999 justify right
column maxquerylen   head "max query|length(sec)"          format 99,999 justify right
column nospaceerrcnt          head "nospace|errors (#)"   format 99,999 justify right
column ssolderrcnt   head "ora-01555|errors(#)"   format 99,999 justify right
select
  to_char(a.begin_time, 'dd-mm-yyyy-hh24')      "hour"
, round(sum(a.activeblks* 8 )/1024)   "activesize"
, round(sum(a.undoblks * 8 )/1024)   "undosize"
, round(sum(a.expiredblks * 8 )/1024)   "expiredsize"
, round(sum(a.unexpiredblks * 8 )/1024)  "unexpiredsize"
, round(sum(a.expblkrelcnt * 8 )/1024)   "expiredrelsize"
, max(maxconcurrency)     maxconcurrency
, sum(txncount)     txncount
, max(maxquerylen)     maxquerylen
, sum(nospaceerrcnt)     nospaceerrcnt
, sum(ssolderrcnt)     ssolderrcnt
from v$undostat a
group by to_char(a.begin_time, 'dd-mm-yyyy-hh24')
order by 1
/



-- for Rac 
set lines 1000  pages 200
column inst_id head inst#                                 format 99
column hour   head "hour|(dd-mm-yyyy-hh24)"                   justify centre
column activesize     head "active|size(mb)"            format 99,999 justify right
column undosize  head "undo|size(mb)"              format 99,999 justify right
column expiredsize head "expired|size(mb)"           format 99,999 justify right
column unexpiredsize  head "unexpired|size(mb)"         format 99,999 justify right
column expiredrelsize  head "expired rel|size(mb)"       format 99,999 justify right
column maxconcurrency  head "max |concurrent|txn(#)"     format 99,999 justify right
column txncount  head "total|txn (#)"              format 99,999 justify right
column maxquerylen  head "max query|length(sec)"      format 99,999 justify right
column nospaceerrcnt  head "nospace|errors (#)"         format 99,999 justify right
column ssolderrcnt  head "ora-01555|errors(#)"        format 99,999 justify right
select
  a.inst_id
, to_char(a.begin_time, 'dd-mm-yyyy-hh24')  hour
, round(sum(a.activeblks* 8 )/1024)   activesize
, round(sum(a.undoblks * 8 )/1024)   undosize
, round(sum(a.expiredblks * 8 )/1024)   expiredsize
, round(sum(a.unexpiredblks * 8 )/1024)  unexpiredsize
, round(sum(a.expblkrelcnt * 8 )/1024)   expiredrelsize
, max(maxconcurrency)     maxconcurrency
, sum(txncount)     txncount
, max(maxquerylen)     maxquerylen
, sum(nospaceerrcnt)     nospaceerrcnt
, sum(ssolderrcnt)     ssolderrcnt
from gv$undostat a
group by a.inst_id, to_char(a.begin_time, 'dd-mm-yyyy-hh24')
order by 2 ;

    hour        active     undo  expired unexpired expired rel concurrent   total   max query    nospace ora-01555
(dd-mm-yyyy-h size(mb) size(mb) size(mb)  size(mb)    size(mb)     txn(#) txn (#) length(sec) errors (#) errors(#)
------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- ---------
12-09-2016-09      488        2      570         4           0          4     181       1,125          0         0
12-09-2016-10    1,463        2    1,717         4           0          4     362       1,121          0         0
12-09-2016-11    1,463        2    1,711         6           0          4     346       1,110          0         0

Rac Output ..

                                                                           max
          hour        active     undo  expired unexpired expired rel concurrent   total   max query    nospace ora-01555
inst# (dd-mm-yyyy-h size(mb) size(mb) size(mb)  size(mb)    size(mb)     txn(#) txn (#) length(sec) errors (#) errors(#)
----- ------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- ---------
    1 12-09-2016-09      976        2    1,141         7           0          4     232       1,128          0         0
    2 12-09-2016-09       63        2    1,697         3           0          3     171       1,070          0         0
    1 12-09-2016-10    1,463        2    1,717         4           0          4     362       1,121          0         0

Oracle ASM balanced files report

set pagesize 55
set linesize 90

SELECT group_kffxp Group#
, number_kffxp file#
, MAX(count1) MAX
, MIN(count1) MIN
FROM
(SELECT group_kffxp
, number_kffxp
, disk_kffxp
, COUNT(XNUM_KFFXP) count1
FROM x$kffxp
WHERE group_kffxp = &diskgroup_number
ANd disk_kffxp != 65534
GROUP BY group_kffxp, number_kffxp, disk_kffxp
ORDER BY group_kffxp
, number_kffxp
, disk_kffxp
)
GROUP BY group_kffxp, number_kffxp;

Oracle ASM usage by file type

ASM usage by file type

set term on


SET pages 32767
SET lines 255
SET numf 999,999

COLUMN NAME HEAD "DiskGroup" FORMAT A15
COLUMN type HEAD "FileType" FORMAT A20
COLUMN SizeGB HEAD "Size|(GB)"

TTITLE LEFT "ASM SPACE USAGE BY FILETYPE"

BREAK ON REPORT
BREAK ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON REPORT

select dg.name
, f.type
, ROUND(sum(bytes)/1024/1024/1024) SizeGB
from v$asm_file f
, v$asm_diskgroup dg
where dg.group_number = f.group_number
group by dg.name, f.type
ORDER BY dg.name, f.type ;


TTITLE OFF

Oracle ASM Disk fail group

ASM Disk fail group

set term on
set lines 250
set pages 50

COLUMN DiskGroup FORMAT A10
COLUMN Disk FORMAT A30
COLUMN Partner_Disk FORMAT A30
COLUMN DiskGroup FORMAT A0
COLUMN FAILGROUP FORMAT A20
COLUMN PARTNER_FAILGROUP FORMAT A20
COLUMN path FORMAT A30
COLUMN PARTNER_PATH FORMAT A30

SELECT dg1.name DiskGroup
, d1.NAME Disk
-- , d1.path
, d1.FAILGROUP
, d2.name Partner_Disk
, d2.FAILGROUP PARTNER_FAILGROUP
-- , d2.path PARTNER_PATH
FROM x$kfdpartner p
, v$asm_disk d1
, v$asm_diskgroup dg1
, v$asm_disk d2
WHERE dg1.group_number = d1.group_number
AND p.GRP = dg1.group_number
AND p.disk = d1.DISK_NUMBER
AND p.GRP = d2.group_number (+)
AND p.NUMBER_KFDPARTNER = d2.DISK_NUMBER (+)
AND dg1.name like '%%' --DiskGroup Name
AND d1.name like '%%' --Disk Name
ORDER BY dg1.name , d1.NAME;

Oracle ASM diskgrop report .. 

set feedback off pagesize 1000 head on  underline _ linesize 170 
var v number ;
begin :v := nvl('&v',95); end;
/
col  total_gb        format 99999.99      heading 'total gb '
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col  COMPATIBILITY        for a12                                        
col DATABASE_COMPATIBILITY for a15
col  total_gb        format 99999.99      heading 'total gb '
col host_name for a15
set linesize 400
select    
substr(max (SYS_CONTEXT('USERENV','HOST')),1,15) host_name
,g.group_number
         , g.name
         , g.state
,min(compatibility) compatibility, min(database_compatibility) database_compatibility
,max(Type) type
,max(allocation_unit_size/1024/1024) allocation_unit_size_MB
         , g.total_mb/1024            total_gb
         , trunc((g.free_mb/1024),2)  free_gb
         , usable_file_mb           usable_file_mb
         , count(DISK_NUMBER) Total_Disks
         , trunc(max(d.TOTAL_MB/1024),2) disk_size
         , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
-- and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
--and g.group_number not in (select v1.group_number from v$asm_volume v1)
--and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2)) 
--ORDER BY "TotalUsed%" desc
order by 2
 ;

set term on pages 200 lines 255 numf 999,999
COLUMN NAME                    HEAD "DiskGroup"    FORMAT A15
COLUMN type                    HEAD "Type"         FORMAT A6
COLUMN compatibility           HEAD "ASM|Compat"   FORMAT A10
COLUMN database_compatibility  HEAD "RDBMS|Compat" FORMAT A10
COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 99999999
COLUMN offline_disks           HEAD "Offline|Disks"
COLUMN TOTAL_GB                HEAD "Total|(GB)"
COLUMN FREE_GB                 HEAD "Free|(GB)"
COLUMN used_GB                 HEAD "Used|(GB)"
COLUMN hot_used_GB             HEAD "Hot|Used|(GB)"
COLUMN cold_used_GB            HEAD "Cold|Used|(GB)"
COLUMN REQUIRED_MIRROR_FREE_GB HEAD "Required|Free|Mirror|(GB)" JUSTIFY RIGHT COLUMN USABLE_GB HEAD "Usable|Free|(GB)"
TTITLE LEFT "ASM DISKGROUP SPACE USAGE REPORT"
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF TOTAL_GB     FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF FREE_GB      FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF USED_GB      FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF HOT_USED_GB  FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT
-- if disk not mounted with instance their values will be 0.
SELECT NAME
, state
, type
, compatibility
, database_compatibility
, allocation_unit_size/1024/1024 allocation_unit_size_MB
, offline_disks
, ROUND(TOTAL_MB/1024) TOTAL_GB
, ROUND(FREE_MB/1024) FREE_GB
, ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB
, ROUND(hot_used_mb/1024) HOT_USED_GB
, ROUND(cold_used_mb/1024) COLD_USED_GB
, ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB
, ROUND(USABLE_FILE_MB /1024) USABLE_GB
FROM v$asm_diskgroup;

===

var v number ;

begin :v := nvl('&v',90); end;

/

/

set term on pages 200 lines 255 numf 999,999

col   name                    head "diskgroup"    format a10

col   type                    head "type"        format a6

col   compatibility           head "asm|compat"    format a10

col   database_compatibility  head "rdbms|compat" format a10

col   allocation_unit_size_mb head "au|size|(mb)" format 999

col   offline_disks           head "offline|disks"

col   total_gb                head "total|(gb)"

col   free_gb                 head "free|(gb)"

col   used_gb                 head "used|(gb)"

col   hot_used_gb             head "hot|used|(gb)"

col   cold_used_gb            head "cold|used|(gb)"

col attention   for a12

with disk as (select 

d.group_number                  gp_num

, count(d.DISK_NUMBER)          Total_Disks

, trunc(max(d.TOTAL_MB/1024),2) disk_size

from v$asm_disk d

group by d.group_number )

select 

  g.name

, di.total_disks

, di.disk_size

, g.state

, g.type

, g.compatibility

, g.database_compatibility

, g.allocation_unit_size/1024/1024 allocation_unit_size_MB

, g.offline_disks

, round(g.total_mb/1024) total_gb

, round(g.free_mb/1024)  free_gb

, round((g.hot_used_mb + cold_used_mb) /1024) used_gb

, round(g.hot_used_mb/1024)              hot_used_gb

, round(g.cold_used_mb/1024)             cold_used_gb

, round(g.required_mirror_free_mb/1024)  required_mirror_free_gb

, round(g.usable_file_mb /1024)          usable_gb

, (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"

, case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'

          else 'Good'

         end as attention

FROM v$asm_diskgroup g, disk di

where 1=1

and   g.Name not in ('GRID' )

and  g.group_number=di.gp_num ;

=====================

SET LINESIZE  200 PAGESIZE  9999 

COLUMN disk_group_name        FORMAT a20           HEAD 'Disk Group Name'

COLUMN disk_file_path         FORMAT a17           HEAD 'Path'

COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'

COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'

COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'

COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'

COLUMN pct_used               FORMAT 9999.99       HEAD 'Pct. Used'

break on report on disk_group_name  skip 1

compute sum label ""              of total_mb used_mb on disk_group_name

compute sum label "Grand Total: " of total_mb used_mb on report

SELECT

    NVL(a.name, '[CANDIDATE]')                       disk_group_name

  , b.path                                           disk_file_path

  , b.name                                           disk_file_name

  , b.failgroup                                      disk_file_fail_group

  , b.total_mb                                       total_mb

  , (b.total_mb - b.free_mb)                         used_mb

  , ROUND((1- (b.free_mb / nullif(b.total_mb,0)))*100, 2) ||'%'     pct_used

FROM    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY   a.name

/

Disk Group Name      Path              File Name            Fail Group           File Size (MB) Used Size (MB) Pct. Used

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

DATA                 ORCL:FLSH_DATA0   FLSH_DATA0           FLSH_DATA0                  102,401         44,067 43.03%

                     ORCL:FLSH_DATA1   FLSH_DATA1           FLSH_DATA1                  102,401         44,058 43.02%

                     ORCL:FLSH_DATA2   FLSH_DATA2           FLSH_DATA2                  102,401         44,053 43.02%

                     ORCL:FLSH_DATA3   FLSH_DATA3           FLSH_DATA3                  102,401         44,055 43.02%

********************                                                             -------------- --------------

                                                                                        409,604        176,233

DUMPS                ORCL:FLSH_DUMPS0  FLSH_DUMPS0          FLSH_DUMPS0                 512,000        501,900 98.03%

********************                                                             -------------- --------------

                                                                                        512,000        501,900

GRID                 ORCL:FLSH_GRID0   FLSH_GRID0           FLSH_GRID0                   40,961            396 .97%

********************                                                             -------------- --------------

                                                                                         40,961            396

LOGS                 ORCL:FLSH_LOGS0   FLSH_LOGS0           FLSH_LOGS0                  102,401         38,704 37.8%

********************                                                             -------------- --------------

                                                                                        102,401         38,704

MGMT                 ORCL:FLSH_MGMT0   FLSH_MGMT0           FLSH_MGMT0                  102,400         55,080 53.79%

********************                                                             -------------- --------------

                                                                                        102,400         55,080

[CANDIDATE]          ORCL:FLSH_RECO0                                                          0              0 %

********************                                                             -------------- --------------

                                                                                              0              0

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

Grand Total:                                                                          1,167,366        772,313

9 rows selected.

============

COLUMN name NEW_VALUE _instname NOPRINT

select lower(instance_name) name from v$instance

set lines 600

col state format a9

col dgname format a15

col sector format 999990

col block format 999990

col label format a25

col path format a40

col redundancy format a25

col pct_used format 990

col pct_free format 990

col voting format a6   

BREAK ON REPORT

COMPUTE SUM OF raw_gb ON REPORT 

COMPUTE SUM OF usable_total_gb ON REPORT 

COMPUTE SUM OF usable_used_gb ON REPORT 

COMPUTE SUM OF usable_free_gb ON REPORT 

COMPUTE SUM OF required_mirror_free_gb ON REPORT 

COMPUTE SUM OF usable_file_gb ON REPORT 

COL name NEW_V _hostname NOPRINT

select lower(host_name) name from v$instance;

select 

        trim('&_hostname') hostname,

        name as dgname,

        state,

        type,

        sector_size sector,

        block_size block,

        allocation_unit_size au,

        round(total_mb/1024,2) raw_gb,

        round((DECODE(TYPE, 'HIGH', 0.3333 * total_mb, 'NORMAL', .5 * total_mb, total_mb))/1024,2) usable_total_gb,

        round((DECODE(TYPE, 'HIGH', 0.3333 * (total_mb - free_mb), 'NORMAL', .5 * (total_mb - free_mb), (total_mb - free_mb)))/1024,2) usable_used_gb,

        round((DECODE(TYPE, 'HIGH', 0.3333 * free_mb, 'NORMAL', .5 * free_mb, free_mb))/1024,2) usable_free_gb,

        round((DECODE(TYPE, 'HIGH', 0.3333 * required_mirror_free_mb, 'NORMAL', .5 * required_mirror_free_mb, required_mirror_free_mb))/1024,2) required_mirror_free_gb,

        round(usable_file_mb/1024,2) usable_file_gb,

        round((total_mb - free_mb)/total_mb,2)*100 as "PCT_USED", 

        round(free_mb/total_mb,2)*100 as "PCT_FREE",

        offline_disks,

        voting_files voting

from v$asm_diskgroup

where total_mb != 0

order by 1;

===

set numf 99999.99

col DiskCnt for 9999

col "Group" for 999

SELECT g.group_number  "Group" ,  g.name   "Group Name" ,  g.state  "State" ,  g.type  "Type" , g.total_mb/1024 "Total GB" , g.free_mb/1024  "Free GB" ,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance" 

,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance" 

,      100*(min(d.free_mb/d.total_mb)) "MinFree" 

,      100*(max(d.free_mb/d.total_mb)) "MaxFree" 

,      count(*)        "DiskCnt" 

FROM v$asm_disk d, v$asm_diskgroup g 

WHERE d.group_number = g.group_number 

and d.group_number <> 0 

and d.state = 'NORMAL' 

and d.mount_status = 'CACHED' 

GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb 

ORDER BY 1;

=====

from web

 set linesize 300

sset term on

SSET numf 999,999

DEFINE size_label=GB

DEFINE size_divider="1024/1024/1024"

PROMPT **********************************************************************

PROMPT * A S M    D I S K G R O U P    S P A C E   U S A G E   R E P O R T  *

PROMPT **********************************************************************

COLUMN NAME                      HEAD "DiskGroup"                   FORMAT A16

COLUMN group_number              HEAD "G#"                          FORMAT 99

COLUMN type                      HEAD "Redundancy|Type"             FORMAT A6

COLUMN compatibility             HEAD "ASM|Compat"                  FORMAT A10

COLUMN database_compatibility    HEAD "RDBMS|Compat"                FORMAT A10

COLUMN allocation_unit_size_MB   HEAD "AU|Size|(MB)"                FORMAT 999

COLUMN offline_disks             HEAD "Offline|Disks"

COLUMN separator                 HEAD "!|!|!|!|!|!"                 FORMAT a1

COLUMN mirrored_total_GB         HEAD "(E)||Mirrored|Total|(&size_label)"

COLUMN USABLE_TOTAL_GB           HEAD "(A-D)|/REDUN||Safely|Usable|Total|(&size_label)"

COLUMN USABLE_USED_GB            HEAD "(B-D)|/REDUN||Safely|Usable|Used|(&size_label)"

COLUMN USABLE_FREE_GB            HEAD "(C-D)|/REDUN||Safely|Usable|Free|(&size_label)"

COLUMN USABLE_USED_PERCENT       HEAD "Safely|Usable|Used|(%)"   FORMAT 999.99

COLUMN USABLE_FREE_PERCENT       HEAD "Safely|Usable|Free|(%)"   FORMAT 999.99

COLUMN mirrored_free_GB          HEAD "Mirrored|Free|(&size_label)"

COLUMN REQUIRED_MIRROR_FREE_GB   HEAD "(D)|Required|Free|ForMirror|(&size_label)"  JUSTIFY RIGHT

COLUMN TOTAL_GB                  HEAD "(A)||Total|Avail|(&size_label)"

COLUMN used_GB                   HEAD "(B)||Total|Used|(&size_label)"

COLUMN hot_used_GB               HEAD "Hot|Used|(&size_label)"

COLUMN cold_used_GB              HEAD "Cold|Used|(&size_label)"

COLUMN FREE_GB                   HEAD "(C)||Total|Free|(&size_label)"

BREAK ON REPORT

COMPUTE SUM LABEL 'Total' OF TOTAL_GB                 FORMAT 99,999,999  ON REPORT

COMPUTE SUM LABEL 'Total' OF USED_GB                  FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF HOT_USED_GB              FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF COLD_USED_GB             FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF FREE_GB                  FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB  FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF MIRRORED_TOTAL_GB        FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF MIRRORED_USED_GB         FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF MIRRORED_FREE_GB         FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_GB                FORMAT 9,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_TOTAL_GB           FORMAT 9,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_USED_GB           FORMAT 9,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_FREE_GB           FORMAT 9,999,999 ON REPORT

PROMPT

PROMPT .                           <----------------------- With Mirroring ----------------------> <-------------------- Without Mirroring ---------------->

--  Note:

--  The GROUP_NUMBER, TOTAL_MB, and FREE_MB columns are only

--  meaningful if the disk group is mounted by the instance. Otherwise, their values will be 0.

SELECT NAME

  , group_number

  , type

  , '!' separator

  , ROUND(TOTAL_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024)                                            mirrored_TOTAL_GB

  , ROUND((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024)               USABLE_TOTAL_GB

  , ROUND((hot_used_mb + cold_used_mb)/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3) /1024)                       USABLE_USED_GB

  , ROUND(USABLE_FILE_MB /1024)                                                                                 USABLE_FREE_GB

  , ROUND((USABLE_FILE_MB + ROUND(REQUIRED_MIRROR_FREE_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) ) /1024) mirrored_free_GB

  , ROUND( (hot_used_mb + cold_used_mb) / (TOTAL_MB - REQUIRED_MIRROR_FREE_MB ) * 100 , 2)                      USABLE_USED_PERCENT

  , ROUND(USABLE_FILE_MB /((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) * 100 ,2) USABLE_FREE_PERCENT

  , '!' separator

  , ROUND(REQUIRED_MIRROR_FREE_MB/1024)       REQUIRED_MIRROR_FREE_GB

  , ROUND(TOTAL_MB/1024)                      TOTAL_GB

  , ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB

  , ROUND(hot_used_mb/1024)                   HOT_USED_GB

  , ROUND(cold_used_mb/1024)                  COLD_USED_GB

  , ROUND(FREE_MB/1024)                       FREE_GB

  , '!' separator

 FROM v$asm_diskgroup_stat;

===================

DiskGroup space info 

		 

set feedback off pagesize 1000 head on  underline _ linesize 170 
var v number ;
begin :v := nvl('&v',95); end;
/

set linesize 400
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col  COMPATIBILITY        for a12                                        
col DATABASE_COMPATIBILITY for a15
--col  total_gb        format 99999.99      heading 'total gb '
col host_name for a16
col  total_gb        format 999,999,999,999      heading 'total gb '
col STATE for a12
col type for a12
select    
substr(max (SYS_CONTEXT('USERENV','HOST')),1,16) host_name
,g.group_number
         , g.name
         , g.state
,min(compatibility) compatibility, min(database_compatibility) database_compatibility
,max(Type) type
         , g.total_mb/1024            total_gb
         , trunc((g.free_mb/1024),2)  free_gb
         , usable_file_mb           usable_file_mb
         , count(DISK_NUMBER) Total_Disks
         , trunc(max(d.TOTAL_MB/1024),2) disk_size
         , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
-- and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
--and g.group_number not in (select v1.group_number from v$asm_volume v1)
--and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2)) 
--ORDER BY "TotalUsed%" desc
order by 2
 ;
 

as grid 

[grid@ ~]$ sqlplus / as sysasm  --- <<<<<<<<<<<<<<  from grid  

var v number ;
begin :v := nvl('&v',95); end;
/

set feedback off pagesize 1000 head off  underline _ linesize 190
ttitle  off
column  today noprint new_value strtoday
select  to_char( sysdate, 'dd mon yyyy hh24:mi:ss' ) today from dual;
column  database_name noprint new_value strdatabasename
select  'Asm usage Statistics for ON '||HOST_NAME  as database_name from v$instance ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'ASM Usage' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
set  head on
col  total_gb        format 999,999,999      heading 'total gb '
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col  type            format a11           heading 'Redundancy'
col VOTING_FILES for A11
col COMPATIBILITY for a12 
col DATABASE_COMP for a12 
select    g.group_number
         , g.name
         , g.state
		 ,g.type
		 ,g.VOTING_FILES
		 ,max(COMPATIBILITY )     COMPATIBILITY                                                                                   
         ,max(DATABASE_COMPATIBILITY)    DATABASE_COMP
         , g.total_mb/1024            total_gb
         , trunc((g.free_mb/1024),2)  free_gb
         , usable_file_mb           usable_file_mb
         , count(DISK_NUMBER) Total_Disks
         , trunc(max(d.TOTAL_MB/1024),2) disk_size
         , (round((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
-- and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
and g.group_number not in (select v1.group_number from v$asm_volume v1)  --- to avoid acfs !!! run as grid!!
--and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state,g.type ,g.VOTING_FILES,g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)) 
ORDER BY "TotalUsed%" desc ;





______________________________________________________________________________________________________________________

                                                                           21 apr 2024 09:14:19
                                                                                 ASM Usage
                                                         Asm usage Statistics for ON rac01
______________________________________________________________________________________________________________________

GROUP_NUMBER NAME            STATE       Redundancy  VOTING_FILE COMPATIBILIT DATABASE_COM    total gb     FREE_GB USABLE_FILE_MB TOTAL_DISKS  DISK_SIZE TotalUsed% ATTENTION
____________ _______________ ___________ ___________ ___________ ____________ ____________ ____________ __________ ______________ ___________ __________ __________ ____________________
           0 MGMT            DISMOUNTED              N           0.0.0.0.0    0.0.0.0.0               0          0              0           2          0            Good
           0 RECO            DISMOUNTED              N           0.0.0.0.0    0.0.0.0.0               0          0              0           2          0            Good
           3 LOGS            MOUNTED     EXTERN      N           12.1.0.0.0   10.1.0.0.0            100      97.49          99835           1        100       2.51 Good
           2 GRID            MOUNTED     EXTERN      Y           12.1.0.0.0   10.1.0.0.0             40      39.57          40525           1         40       1.06 Good

var v number ;
begin :v := nvl('&v',93); end;
/

set feedback off pagesize 1000 head off  underline _ linesize 200 
ttitle  off
column  today noprint new_value strtoday
select  to_char( sysdate, 'dd mon yyyy hh24:mi:ss' ) today from dual;
column  database_name noprint new_value strdatabasename
select  'Asm usage Statistics for ON '||HOST_NAME ||'-' || Db_unique_name as database_name from v$database,v$instance ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'ASM Usage' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
set  head on
col  total_gb        format 999,999,999      heading 'total gb '
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col Redundancy for a15
col free for a8
col disk_size for a10
col TOTAL for a8
col usable_file for a12
col COMPATIBILITY for a10
col DATABASE_COMP for a10
col VOTING_FILES for a12
select    g.group_number
         , g.name
         , g.state
		 ,g.type
		 ,g.VOTING_FILES
		 ,max(COMPATIBILITY )     COMPATIBILITY                                                                                   
         ,max(DATABASE_COMPATIBILITY)    DATABASE_COMP
         , upper(dbms_xplan.format_size2(g.total_mb*1024*1024 ))          total
		 ,max(TYPE) Redundancy
         , dbms_xplan.format_size(g.free_mb*1024*1024)  free
         , dbms_xplan.format_size(usable_file_mb* 1024*1024)          usable_file
         , count(DISK_NUMBER) Total_Disks
         , dbms_xplan.format_size(max(d.TOTAL_MB*1024*1024)) disk_size
         , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
and g.group_number not in (select v1.group_number from v$asm_volume v1)
and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.type	 ,g.VOTING_FILES,g.state, g.total_mb*1024*1024, (g.free_mb*1024*1024), usable_file_mb* 1024*1024 ,(round((1- (g.free_mb / g.total_mb))*100, 2)) 
ORDER BY "TotalUsed%" desc ;

from grid if   proxy asm instance 

export ORACLE_HOME=/u01/app/12.1.0/grid 
 export ORACLE_SID=+APX2 

 select * from v$asm_filesystem;