{"id":82,"date":"2012-03-01T02:30:29","date_gmt":"2012-03-01T02:30:29","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=82"},"modified":"2015-07-27T02:45:46","modified_gmt":"2015-07-27T02:45:46","slug":"asm","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=82","title":{"rendered":"ASM"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nAMS diskgroups<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSET LINESIZE  145\r\nSET PAGESIZE  9999\r\nSET VERIFY    off\r\n\r\nCOLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'\r\nCOLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'\r\nCOLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'\r\nCOLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'\r\nCOLUMN state                  FORMAT a11           HEAD 'State'\r\nCOLUMN type                   FORMAT a6            HEAD 'Type'\r\nCOLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'\r\nCOLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'\r\nCOLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'\r\n\r\nbreak on report on disk_group_name skip 1\r\n\r\ncompute sum label \"Grand Total: \" of total_mb used_mb on report\r\n\r\nSELECT\r\n    name                                     group_name\r\n  , sector_size                              sector_size\r\n  , block_size                               block_size\r\n  , allocation_unit_size                     allocation_unit_size\r\n  , state                                    state\r\n  , type                                     type\r\n  , total_mb                                 total_mb\r\n  , (total_mb - free_mb)                     used_mb\r\n  , ROUND((1- (free_mb \/ total_mb))*100, 2)  pct_used\r\nFROM\r\n    v$asm_diskgroup\r\nORDER BY\r\n    name\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE  145<br \/>\nSET PAGESIZE  9999<br \/>\nSET VERIFY    off<\/p>\n<p>COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'<br \/>\nCOLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'<br \/>\nCOLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'<br \/>\nCOLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'<br \/>\nCOLUMN state                  FORMAT a11           HEAD 'State'<br \/>\nCOLUMN type                   FORMAT a6            HEAD 'Type'<br \/>\nCOLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'<br \/>\nCOLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'<br \/>\nCOLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'<\/p>\n<p>break on report on disk_group_name skip 1<\/p>\n<p>compute sum label \"Grand Total: \" of total_mb used_mb on report<\/p>\n<p>SELECT<br \/>\n    name                                     group_name<br \/>\n  , sector_size                              sector_size<br \/>\n  , block_size                               block_size<br \/>\n  , allocation_unit_size                     allocation_unit_size<br \/>\n  , state                                    state<br \/>\n  , type                                     type<br \/>\n  , total_mb                                 total_mb<br \/>\n  , (total_mb - free_mb)                     used_mb<br \/>\n  , ROUND((1- (free_mb \/ total_mb))*100, 2)  pct_used<br \/>\nFROM<br \/>\n    v$asm_diskgroup<br \/>\nORDER BY<br \/>\n    name<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nAMD disks<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSET LINESIZE  145\r\nSET PAGESIZE  9999\r\nSET VERIFY    off\r\n\r\nCOLUMN disk_group_name        FORMAT a20           HEAD 'Disk Group Name'\r\nCOLUMN disk_file_path         FORMAT a17           HEAD 'Path'\r\nCOLUMN disk_file_name         FORMAT a20           HEAD 'File Name'\r\nCOLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'\r\nCOLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'\r\nCOLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'\r\nCOLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'\r\n\r\nbreak on report on disk_group_name skip 1\r\n\r\ncompute sum label \"\"              of total_mb used_mb on disk_group_name\r\ncompute sum label \"Grand Total: \" of total_mb used_mb on report\r\n\r\nSELECT\r\n    NVL(a.name, '[CANDIDATE]')                       disk_group_name\r\n  , b.path                                           disk_file_path\r\n  , b.name                                           disk_file_name\r\n  , b.failgroup                                      disk_file_fail_group\r\n  , b.total_mb                                       total_mb\r\n  , (b.total_mb - b.free_mb)                         used_mb\r\n  , ROUND((1- (b.free_mb \/ b.total_mb))*100, 2)      pct_used\r\nFROM\r\n    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)\r\nORDER BY\r\n    a.name\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE  145<br \/>\nSET PAGESIZE  9999<br \/>\nSET VERIFY    off<\/p>\n<p>COLUMN disk_group_name        FORMAT a20           HEAD 'Disk Group Name'<br \/>\nCOLUMN disk_file_path         FORMAT a17           HEAD 'Path'<br \/>\nCOLUMN disk_file_name         FORMAT a20           HEAD 'File Name'<br \/>\nCOLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'<br \/>\nCOLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'<br \/>\nCOLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'<br \/>\nCOLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'<\/p>\n<p>break on report on disk_group_name skip 1<\/p>\n<p>compute sum label \"\"              of total_mb used_mb on disk_group_name<br \/>\ncompute sum label \"Grand Total: \" of total_mb used_mb on report<\/p>\n<p>SELECT<br \/>\n    NVL(a.name, '[CANDIDATE]')                       disk_group_name<br \/>\n  , b.path                                           disk_file_path<br \/>\n  , b.name                                           disk_file_name<br \/>\n  , b.failgroup                                      disk_file_fail_group<br \/>\n  , b.total_mb                                       total_mb<br \/>\n  , (b.total_mb - b.free_mb)                         used_mb<br \/>\n  , ROUND((1- (b.free_mb \/ b.total_mb))*100, 2)      pct_used<br \/>\nFROM<br \/>\n    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)<br \/>\nORDER BY<br \/>\n    a.name<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nASM files 11g<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSET LINESIZE  150\r\nSET PAGESIZE  9999\r\nSET VERIFY    off\r\n\r\nCOLUMN full_path              FORMAT a63                  HEAD 'ASM File Name \/ Volume Name \/ Device Name'\r\nCOLUMN system_created         FORMAT a8                   HEAD 'System|Created?'\r\nCOLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'\r\nCOLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'\r\nCOLUMN type                   FORMAT a18                  HEAD 'File Type'\r\nCOLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'\r\nCOLUMN striped                FORMAT a8                   HEAD 'Striped'\r\nCOLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'\r\nCOLUMN disk_group_name        noprint\r\n\r\nBREAK ON report ON disk_group_name SKIP 1\r\n\r\ncompute sum label \"\"              of bytes space on disk_group_name\r\ncompute sum label \"Grand Total: \" of bytes space on report\r\n\r\nSELECT\r\n    CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '\/')) full_path\r\n  , db_files.bytes\r\n  , db_files.space\r\n  , NVL(LPAD(db_files.type, 18), '<DIRECTORY>')  type\r\n  , db_files.creation_date\r\n  , db_files.disk_group_name\r\n  , LPAD(db_files.system_created, 4) system_created\r\nFROM\r\n    ( SELECT\r\n          g.name               disk_group_name\r\n        , a.parent_index       pindex\r\n        , a.name               alias_name\r\n        , a.reference_index    rindex\r\n        , a.system_created     system_created\r\n        , f.bytes              bytes\r\n        , f.space              space\r\n        , f.type               type\r\n        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date\r\n      FROM\r\n          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)\r\n                                   JOIN v$asm_diskgroup g USING (group_number)\r\n    ) db_files\r\nWHERE db_files.type IS NOT NULL\r\nSTART WITH (MOD(db_files.pindex, POWER(2, 24))) = 0\r\n    CONNECT BY PRIOR db_files.rindex = db_files.pindex\r\nUNION\r\nSELECT\r\n    '+' || volume_files.disk_group_name ||  ' [' || volume_files.volume_name || '] ' ||  volume_files.volume_device full_path\r\n  , volume_files.bytes\r\n  , volume_files.space\r\n  , NVL(LPAD(volume_files.type, 18), '<DIRECTORY>')  type\r\n  , volume_files.creation_date\r\n  , volume_files.disk_group_name\r\n  , null\r\nFROM\r\n    ( SELECT\r\n          g.name               disk_group_name\r\n        , v.volume_name        volume_name\r\n        , v.volume_device       volume_device\r\n        , f.bytes              bytes\r\n        , f.space              space\r\n        , f.type               type\r\n        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date\r\n      FROM\r\n          v$asm_file f RIGHT OUTER JOIN v$asm_volume    v USING (group_number, file_number)\r\n                                   JOIN v$asm_diskgroup g USING (group_number)\r\n    ) volume_files\r\nWHERE volume_files.type IS NOT NULL\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE  150<br \/>\nSET PAGESIZE  9999<br \/>\nSET VERIFY    off<\/p>\n<p>COLUMN full_path              FORMAT a63                  HEAD 'ASM File Name \/ Volume Name \/ Device Name'<br \/>\nCOLUMN system_created         FORMAT a8                   HEAD 'System|Created?'<br \/>\nCOLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'<br \/>\nCOLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'<br \/>\nCOLUMN type                   FORMAT a18                  HEAD 'File Type'<br \/>\nCOLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'<br \/>\nCOLUMN striped                FORMAT a8                   HEAD 'Striped'<br \/>\nCOLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'<br \/>\nCOLUMN disk_group_name        noprint<\/p>\n<p>BREAK ON report ON disk_group_name SKIP 1<\/p>\n<p>compute sum label \"\"              of bytes space on disk_group_name<br \/>\ncompute sum label \"Grand Total: \" of bytes space on report<\/p>\n<p>SELECT<br \/>\n    CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '\/')) full_path<br \/>\n  , db_files.bytes<br \/>\n  , db_files.space<br \/>\n  , NVL(LPAD(db_files.type, 18), '<DIRECTORY>')  type<br \/>\n  , db_files.creation_date<br \/>\n  , db_files.disk_group_name<br \/>\n  , LPAD(db_files.system_created, 4) system_created<br \/>\nFROM<br \/>\n    ( SELECT<br \/>\n          g.name               disk_group_name<br \/>\n        , a.parent_index       pindex<br \/>\n        , a.name               alias_name<br \/>\n        , a.reference_index    rindex<br \/>\n        , a.system_created     system_created<br \/>\n        , f.bytes              bytes<br \/>\n        , f.space              space<br \/>\n        , f.type               type<br \/>\n        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date<br \/>\n      FROM<br \/>\n          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)<br \/>\n                                   JOIN v$asm_diskgroup g USING (group_number)<br \/>\n    ) db_files<br \/>\nWHERE db_files.type IS NOT NULL<br \/>\nSTART WITH (MOD(db_files.pindex, POWER(2, 24))) = 0<br \/>\n    CONNECT BY PRIOR db_files.rindex = db_files.pindex<br \/>\nUNION<br \/>\nSELECT<br \/>\n    '+' || volume_files.disk_group_name ||  ' [' || volume_files.volume_name || '] ' ||  volume_files.volume_device full_path<br \/>\n  , volume_files.bytes<br \/>\n  , volume_files.space<br \/>\n  , NVL(LPAD(volume_files.type, 18), '<DIRECTORY>')  type<br \/>\n  , volume_files.creation_date<br \/>\n  , volume_files.disk_group_name<br \/>\n  , null<br \/>\nFROM<br \/>\n    ( SELECT<br \/>\n          g.name               disk_group_name<br \/>\n        , v.volume_name        volume_name<br \/>\n        , v.volume_device       volume_device<br \/>\n        , f.bytes              bytes<br \/>\n        , f.space              space<br \/>\n        , f.type               type<br \/>\n        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date<br \/>\n      FROM<br \/>\n          v$asm_file f RIGHT OUTER JOIN v$asm_volume    v USING (group_number, file_number)<br \/>\n                                   JOIN v$asm_diskgroup g USING (group_number)<br \/>\n    ) volume_files<br \/>\nWHERE volume_files.type IS NOT NULL<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nAMS files 10g<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSET LINESIZE  150\r\nSET PAGESIZE  9999\r\nSET VERIFY    off\r\n\r\nCOLUMN full_alias_path        FORMAT a63                  HEAD 'File Name'\r\nCOLUMN system_created         FORMAT a8                   HEAD 'System|Created?'\r\nCOLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'\r\nCOLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'\r\nCOLUMN type                   FORMAT a18                  HEAD 'File Type'\r\nCOLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'\r\nCOLUMN striped                FORMAT a8                   HEAD 'Striped'\r\nCOLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'\r\nCOLUMN disk_group_name        noprint\r\n\r\nBREAK ON report ON disk_group_name SKIP 1\r\n\r\ncompute sum label \"\"              of bytes space on disk_group_name\r\ncompute sum label \"Grand Total: \" of bytes space on report\r\n\r\nSELECT\r\n    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '\/')) full_alias_path\r\n  , bytes\r\n  , space\r\n  , NVL(LPAD(type, 18), '<DIRECTORY>')  type\r\n  , creation_date\r\n  , disk_group_name\r\n  , LPAD(system_created, 4) system_created\r\nFROM\r\n    ( SELECT\r\n          g.name               disk_group_name\r\n        , a.parent_index       pindex\r\n        , a.name               alias_name\r\n        , a.reference_index    rindex\r\n        , a.system_created     system_created\r\n        , f.bytes              bytes\r\n        , f.space              space\r\n        , f.type               type\r\n        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date\r\n      FROM\r\n          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)\r\n                                   JOIN v$asm_diskgroup g USING (group_number)\r\n    )\r\nWHERE type IS NOT NULL\r\nSTART WITH (MOD(pindex, POWER(2, 24))) = 0\r\n    CONNECT BY PRIOR rindex = pindex\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE  150<br \/>\nSET PAGESIZE  9999<br \/>\nSET VERIFY    off<\/p>\n<p>COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name'<br \/>\nCOLUMN system_created         FORMAT a8                   HEAD 'System|Created?'<br \/>\nCOLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'<br \/>\nCOLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'<br \/>\nCOLUMN type                   FORMAT a18                  HEAD 'File Type'<br \/>\nCOLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'<br \/>\nCOLUMN striped                FORMAT a8                   HEAD 'Striped'<br \/>\nCOLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'<br \/>\nCOLUMN disk_group_name        noprint<\/p>\n<p>BREAK ON report ON disk_group_name SKIP 1<\/p>\n<p>compute sum label \"\"              of bytes space on disk_group_name<br \/>\ncompute sum label \"Grand Total: \" of bytes space on report<\/p>\n<p>SELECT<br \/>\n    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '\/')) full_alias_path<br \/>\n  , bytes<br \/>\n  , space<br \/>\n  , NVL(LPAD(type, 18), '<DIRECTORY>')  type<br \/>\n  , creation_date<br \/>\n  , disk_group_name<br \/>\n  , LPAD(system_created, 4) system_created<br \/>\nFROM<br \/>\n    ( SELECT<br \/>\n          g.name               disk_group_name<br \/>\n        , a.parent_index       pindex<br \/>\n        , a.name               alias_name<br \/>\n        , a.reference_index    rindex<br \/>\n        , a.system_created     system_created<br \/>\n        , f.bytes              bytes<br \/>\n        , f.space              space<br \/>\n        , f.type               type<br \/>\n        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date<br \/>\n      FROM<br \/>\n          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)<br \/>\n                                   JOIN v$asm_diskgroup g USING (group_number)<br \/>\n    )<br \/>\nWHERE type IS NOT NULL<br \/>\nSTART WITH (MOD(pindex, POWER(2, 24))) = 0<br \/>\n    CONNECT BY PRIOR rindex = pindex<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nDatabase files on ASM<br \/>\n<\/h2>\n<p>FILETYPE is one of (can be empty): CONTROLFILE, DATAFILE, ONLINELOG, ARCHIVELOG, TEMPFILE, BACKUPSET, XTRANSPORT, PARAMETERFILE, DATAGUARDCONFIG, FLASHBACK, CHANGETRACKING, DUMPSET, AUTOBACKUP<\/p>\n<pre class=\"qcode\">\r\nset linesize 230\r\nset pagesize 9999\r\ncolumn full_alias_path format a80\r\ncolumn file_type format a15\r\ncolumn size_gb FORMAT 999,999.99     HEADING 'Size (GB)'\r\ncolumn allocated_GB FORMAT 999,999.99     HEADING 'Allocated (GB)'\r\n\r\nbreak on report on file_type skip 1\r\n\r\ncompute sum label \"\"              of size_gb allocated_GB on file_type\r\ncompute sum label \"Grand Total: \" of size_gb allocated_GB on report\r\n\r\nselect file_type, concat('+'||gname, sys_connect_by_path(aname, '\/')) full_alias_path, \r\n       size_GB, allocated_GB, redundancy\r\nfrom ( select b.name gname, a.parent_index pindex, a.name aname, \r\n              a.reference_index rindex , a.system_created, a.alias_directory, c.redundancy,\r\n              c.type file_type, c.bytes\/1024\/1024\/1024  size_GB, c.space\/1024\/1024\/1024 allocated_GB\t  \r\n       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c\r\n       where a.group_number = b.group_number\r\n             and a.group_number = c.group_number(+)\r\n             and a.file_number = c.file_number(+)\r\n             and a.file_incarnation = c.incarnation(+)\r\n     )\r\nwhere file_type like '%&FILE_TYPE%'\r\nstart with (mod(pindex, power(2, 24))) = 0\r\n            and rindex in \r\n                ( select a.reference_index\r\n                  from v$asm_alias a, v$asm_diskgroup b\r\n                  where a.group_number = b.group_number\r\n                        and (mod(a.parent_index, power(2, 24))) = 0\r\n                        and a.name = '&DATABASENAME'\r\n                )\r\nconnect by prior rindex = pindex\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nset linesize 230<br \/>\nset pagesize 9999<br \/>\ncolumn full_alias_path format a80<br \/>\ncolumn file_type format a15<br \/>\ncolumn size_gb FORMAT 999,999.99     HEADING 'Size (GB)'<br \/>\ncolumn allocated_GB FORMAT 999,999.99     HEADING 'Allocated (GB)'<\/p>\n<p>break on report on file_type skip 1<\/p>\n<p>compute sum label \"\"              of size_gb allocated_GB on file_type<br \/>\ncompute sum label \"Grand Total: \" of size_gb allocated_GB on report<\/p>\n<p>select file_type, concat('+'||gname, sys_connect_by_path(aname, '\/')) full_alias_path,<br \/>\n       size_GB, allocated_GB, redundancy<br \/>\nfrom ( select b.name gname, a.parent_index pindex, a.name aname,<br \/>\n              a.reference_index rindex , a.system_created, a.alias_directory, c.redundancy,<br \/>\n              c.type file_type, c.bytes\/1024\/1024\/1024  size_GB, c.space\/1024\/1024\/1024 allocated_GB<br \/>\n       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c<br \/>\n       where a.group_number = b.group_number<br \/>\n             and a.group_number = c.group_number(+)<br \/>\n             and a.file_number = c.file_number(+)<br \/>\n             and a.file_incarnation = c.incarnation(+)<br \/>\n     )<br \/>\nwhere file_type like '%&FILE_TYPE%'<br \/>\nstart with (mod(pindex, power(2, 24))) = 0<br \/>\n            and rindex in<br \/>\n                ( select a.reference_index<br \/>\n                  from v$asm_alias a, v$asm_diskgroup b<br \/>\n                  where a.group_number = b.group_number<br \/>\n                        and (mod(a.parent_index, power(2, 24))) = 0<br \/>\n                        and a.name = '&DATABASENAME'<br \/>\n                )<br \/>\nconnect by prior rindex = pindex<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nASM usage by filetype for all DBs<br \/>\n<\/h2>\n<p>FILETYPE is one of (can be empty): CONTROLFILE, DATAFILE, ONLINELOG, ARCHIVELOG, TEMPFILE, BACKUPSET, XTRANSPORT, PARAMETERFILE, DATAGUARDCONFIG, FLASHBACK, CHANGETRACKING, DUMPSET, AUTOBACKUP<\/p>\n<pre class=\"qcode\">\r\nset pagesize 9999\r\nset linesize 230\r\n\r\ncol Database form a20 HEADING 'Database Name'\r\ncol type form a20 HEADING 'File type'\r\ncolumn Allocated_GB FORMAT 999,999.99     HEADING 'Allocated (GB)'\r\ncolumn size_gb FORMAT 999,999.99     HEADING 'Size (GB)'\r\n\r\nbreak on report on Database skip 1\r\n\r\ncompute sum label \"\"              of size_gb allocated_GB on Database\r\ncompute sum label \"Grand Total: \" of size_gb allocated_GB on report\r\n\r\nSELECT\r\nSUBSTR(alias_path,2,INSTR(alias_path,'\/',1,2)-2) Database,\r\ntype\r\n,ROUND(SUM(bytes)\/1024\/1024\/1024,2) size_GB\r\n,ROUND(SUM(alloc_bytes)\/1024\/1024\/1024,2) Allocated_GB\r\nFROM\r\n(SELECT\r\nSYS_CONNECT_BY_PATH(alias_name, '\/') alias_path\r\n,alloc_bytes\r\n,bytes\r\n,type\r\nFROM\r\n(SELECT\r\ng.name disk_group_name\r\n, a.parent_index pindex\r\n, a.name alias_name\r\n, a.reference_index rindex\r\n, f.space alloc_bytes\r\n, f.bytes\r\n, f.type type\r\nFROM\r\nv$asm_file f RIGHT OUTER JOIN v$asm_alias a\r\nUSING (group_number, file_number)\r\nJOIN v$asm_diskgroup g\r\nUSING (group_number)\r\n)\r\nWHERE type IS NOT NULL\r\nand type like '%&FILETYPE%'\r\nSTART WITH (MOD(pindex, POWER(2, 24))) = 0\r\nCONNECT BY PRIOR rindex = pindex\r\n)\r\nGROUP BY SUBSTR(alias_path,2,INSTR(alias_path,'\/',1,2)-2), type\r\nORDER BY 1\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nset pagesize 9999<br \/>\nset linesize 230<\/p>\n<p>col Database form a20 HEADING 'Database Name'<br \/>\ncol type form a20 HEADING 'File type'<br \/>\ncolumn Allocated_GB FORMAT 999,999.99     HEADING 'Allocated (GB)'<br \/>\ncolumn size_gb FORMAT 999,999.99     HEADING 'Size (GB)'<\/p>\n<p>break on report on Database skip 1<\/p>\n<p>compute sum label \"\"              of size_gb allocated_GB on Database<br \/>\ncompute sum label \"Grand Total: \" of size_gb allocated_GB on report<\/p>\n<p>SELECT<br \/>\nSUBSTR(alias_path,2,INSTR(alias_path,'\/',1,2)-2) Database,<br \/>\ntype<br \/>\n,ROUND(SUM(bytes)\/1024\/1024\/1024,2) size_GB<br \/>\n,ROUND(SUM(alloc_bytes)\/1024\/1024\/1024,2) Allocated_GB<br \/>\nFROM<br \/>\n(SELECT<br \/>\nSYS_CONNECT_BY_PATH(alias_name, '\/') alias_path<br \/>\n,alloc_bytes<br \/>\n,bytes<br \/>\n,type<br \/>\nFROM<br \/>\n(SELECT<br \/>\ng.name disk_group_name<br \/>\n, a.parent_index pindex<br \/>\n, a.name alias_name<br \/>\n, a.reference_index rindex<br \/>\n, f.space alloc_bytes<br \/>\n, f.bytes<br \/>\n, f.type type<br \/>\nFROM<br \/>\nv$asm_file f RIGHT OUTER JOIN v$asm_alias a<br \/>\nUSING (group_number, file_number)<br \/>\nJOIN v$asm_diskgroup g<br \/>\nUSING (group_number)<br \/>\n)<br \/>\nWHERE type IS NOT NULL<br \/>\nand type like '%&FILETYPE%'<br \/>\nSTART WITH (MOD(pindex, POWER(2, 24))) = 0<br \/>\nCONNECT BY PRIOR rindex = pindex<br \/>\n)<br \/>\nGROUP BY SUBSTR(alias_path,2,INSTR(alias_path,'\/',1,2)-2), type<br \/>\nORDER BY 1<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>AMS diskgroups SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY off COLUMN group_name FORMAT a20 HEAD &#8216;Disk Group|Name&#8217; COLUMN sector_size FORMAT 99,999 HEAD &#8216;Sector|Size&#8217; COLUMN block_size FORMAT 99,999 HEAD &#8216;Block|Size&#8217; COLUMN allocation_unit_size FORMAT 999,999,999 HEAD &#8216;Allocation|Unit Size&#8217; COLUMN state FORMAT a11 HEAD &#8216;State&#8217; COLUMN type FORMAT a6 HEAD &#8216;Type&#8217; COLUMN total_mb FORMAT 999,999,999 HEAD &#8216;Total [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":70,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-82","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-1k","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/82","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=82"}],"version-history":[{"count":9,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/82\/revisions"}],"predecessor-version":[{"id":558,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/82\/revisions\/558"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/70"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=82"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}