AMS diskgroups
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
AMD disks
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
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 999.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 / b.total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name
/
ASM files 11g
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN full_path FORMAT a63 HEAD 'ASM File Name / Volume Name / Device Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.bytes
, db_files.space
, NVL(LPAD(db_files.type, 18), '') type
, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
'+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path
, volume_files.bytes
, volume_files.space
, NVL(LPAD(volume_files.type, 18), '') type
, volume_files.creation_date
, volume_files.disk_group_name
, null
FROM
( SELECT
g.name disk_group_name
, v.volume_name volume_name
, v.volume_device volume_device
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_volume v USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) volume_files
WHERE volume_files.type IS NOT NULL
/
AMS files 10g
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN full_alias_path FORMAT a63 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
SELECT
CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, bytes
, space
, NVL(LPAD(type, 18), '') type
, creation_date
, disk_group_name
, LPAD(system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/
Database files on ASM
FILETYPE is one of (can be empty): CONTROLFILE, DATAFILE, ONLINELOG, ARCHIVELOG, TEMPFILE, BACKUPSET, XTRANSPORT, PARAMETERFILE, DATAGUARDCONFIG, FLASHBACK, CHANGETRACKING, DUMPSET, AUTOBACKUP
set linesize 230
set pagesize 9999
column full_alias_path format a80
column file_type format a15
column size_gb FORMAT 999,999.99 HEADING 'Size (GB)'
column allocated_GB FORMAT 999,999.99 HEADING 'Allocated (GB)'
break on report on file_type skip 1
compute sum label "" of size_gb allocated_GB on file_type
compute sum label "Grand Total: " of size_gb allocated_GB on report
select file_type, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
size_GB, allocated_GB, redundancy
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory, c.redundancy,
c.type file_type, c.bytes/1024/1024/1024 size_GB, c.space/1024/1024/1024 allocated_GB
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
where file_type like '%&FILE_TYPE%'
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASENAME'
)
connect by prior rindex = pindex
/
ASM usage by filetype for all DBs
FILETYPE is one of (can be empty): CONTROLFILE, DATAFILE, ONLINELOG, ARCHIVELOG, TEMPFILE, BACKUPSET, XTRANSPORT, PARAMETERFILE, DATAGUARDCONFIG, FLASHBACK, CHANGETRACKING, DUMPSET, AUTOBACKUP
set pagesize 9999 set linesize 230 col Database form a20 HEADING 'Database Name' col type form a20 HEADING 'File type' column Allocated_GB FORMAT 999,999.99 HEADING 'Allocated (GB)' column size_gb FORMAT 999,999.99 HEADING 'Size (GB)' break on report on Database skip 1 compute sum label "" of size_gb allocated_GB on Database compute sum label "Grand Total: " of size_gb allocated_GB on report SELECT SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2) Database, type ,ROUND(SUM(bytes)/1024/1024/1024,2) size_GB ,ROUND(SUM(alloc_bytes)/1024/1024/1024,2) Allocated_GB FROM (SELECT SYS_CONNECT_BY_PATH(alias_name, '/') alias_path ,alloc_bytes ,bytes ,type FROM (SELECT g.name disk_group_name , a.parent_index pindex , a.name alias_name , a.reference_index rindex , f.space alloc_bytes , f.bytes , f.type type FROM v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number) JOIN v$asm_diskgroup g USING (group_number) ) WHERE type IS NOT NULL and type like '%&FILETYPE%' START WITH (MOD(pindex, POWER(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex ) GROUP BY SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2), type ORDER BY 1 /