Search This Blog

Total Pageviews

Monday 26 September 2011

Oracle Undo statistics


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


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;



Oracle DBA

anuj blog Archive