SET LINES 1000 FEED OFF
COL PATH FORMAT A30
COL LABEL FORMAT A20
COL GROUP_NUMBER FORMAT 99 HEAD "G#"
COL VOLUME_NUMBER FORMAT 99 HEAD "V#"
COL NUM_VOL FORMAT 99 HEAD "V#"
COL DISK_NUMBER FORMAT 99 HEAD "D#"
COL FILE_NUMBER FORMAT 999 HEAD "F#"
COL FS_NAME FORMAT A30
COL MOUNTPATH FORMAT A30
COL VOLUME_DEVICE FORMAT A30
COL FAILGROUP FORMAT A11 HEAD FGROUP
COL COMPATIBILITY FORMAT A12
COL DATABASE_COMPATIBILITY FORMAT A12
COL FILE_NAME FORMAT A34
COL FILE_TYPE FORMAT A20
COL GROUP_NAME FORMAT A17
COL DISK_NAME FORMAT A28 HEAD DISK_NAME
COL LABEL_NAME FORMAT A28 HEAD LABEL_NAME
COL VOLUME_NAME FORMAT A20 HEAD VOLUME_NAME
COL VOTING FORMAT A6
COL INSTANCE FORMAT A12
COL DB_CLIENT FORMAT A12
COL SOFTWARE FORMAT A12
COL COMPATIBLE FORMAT A12
COL COMPATIBLE_ASM FORMAT A11 HEAD "COMPATIBLE|ASM"
COL COMPATIBLE_RDBMS FORMAT A11 HEAD "COMPATIBLE|RDBMS"
COL COMPATIBLE_ADVM FORMAT A11 HEAD "COMPATIBLE|ADVM"
COL REPAIR_TIME FORMAT A7 HEAD "REPAIR|TIME"
PROMPT
PROMPT ===============> ASM DISKGROUPS <===============
PROMPT
-- sql original
SELECT
GROUP_NUMBER
,NAME GROUP_NAME
,STATE
,TYPE
,ROUND(TOTAL_MB/1024) TOTAL_GB
,ROUND(FREE_MB/1024) FREE_GB
,ROUND(COLD_USED_MB/1024) COLD_USED_GB
,ROUND(USABLE_FILE_MB/1024) USABLE_GB
,SECTOR_SIZE
,BLOCK_SIZE
,ALLOCATION_UNIT_SIZE
,REQUIRED_MIRROR_FREE_MB
,OFFLINE_DISKS
,COMPATIBILITY
,DATABASE_COMPATIBILITY
,VOTING_FILES
FROM V$ASM_DISKGROUP
ORDER BY 1
/
-- sql alterado
SELECT
D.GROUP_NUMBER
,D.NAME GROUP_NAME
,CASE D.STATE WHEN 'CONNECTED' THEN 'MOUNTED' ELSE D.STATE END STATE
,D.TYPE
,ROUND(D.TOTAL_MB/1024) OS_TOTAL_GB
,ROUND(D.TOTAL_MB/1024/2) TOTAL_GB
,ROUND(D.COLD_USED_MB/1024/2) USED_GB
,ROUND(D.FREE_MB/1024/2) FREE_GB
,ROUND(D.REQUIRED_MIRROR_FREE_MB/1024/2) MIRROR_GB
,ROUND(D.USABLE_FILE_MB/1024) USABLE_GB
,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME = 'disk_repair_time' ) REPAIR_TIME
,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME = 'compatible.asm' ) COMPATIBLE_ASM
,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME = 'compatible.rdbms' ) COMPATIBLE_RDBMS
,(SELECT VALUE FROM V$ASM_ATTRIBUTE A WHERE D.GROUP_NUMBER = A.GROUP_NUMBER AND NAME = 'compatible.advm' ) COMPATIBLE_ADVM
FROM V$ASM_DISKGROUP D
ORDER BY 1
/
PROMPT
PROMPT ===============> ASM VOLUMES <===============
PROMPT
SELECT
GROUP_NUMBER
,VOLUME_NUMBER
,VOLUME_NAME
,ROUND(SIZE_MB/1024) SIZE_GB
,VOLUME_DEVICE
,MOUNTPATH
,STATE
,REDUNDANCY
,STRIPE_COLUMNS
,STRIPE_WIDTH_K
,COMPOUND_INDEX
,FILE_NUMBER
,INCARNATION
,DRL_FILE_NUMBER
,RESIZE_UNIT_MB
,USAGE
FROM V$ASM_VOLUME
/
PROMPT
PROMPT ===============> ASM FILESYSTEMS <===============
PROMPT
SELECT
NUM_VOL
,FS_NAME
,AVAILABLE_TIME
,BLOCK_SIZE
,STATE
,CORRUPT
,ROUND(TOTAL_SIZE/1024) SIZE_GB
,ROUND(TOTAL_FREE/1024) FREE_GB
,TOTAL_SNAP_SPACE_USAGE
FROM V$ASM_FILESYSTEM
/
PROMPT
PROMPT ===============> ASM CLIENTS <===============
PROMPT
SELECT
dg.name GROUP_NAME
,SUBSTR(c.instance_name,1,12) AS instance
,rpad(c.db_name,12,' ') DB_CLIENT
,SUBSTR(c.SOFTWARE_VERSION,1,12) AS software
,SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
FROM V$ASM_DISKGROUP dg
JOIN V$ASM_CLIENT c ON (dg.group_number = c.group_number)
/
PROMPT
PROMPT ===============> ASM DISKS <===============
PROMPT
SELECT
D.GROUP_NUMBER
,D.HEADER_STATUS ST_HEADER
,( SELECT G.NAME FROM V$ASM_DISKGROUP G WHERE G.GROUP_NUMBER = D.GROUP_NUMBER ) GROUP_NAME
,D.DISK_NUMBER
,D.NAME DISK_NAME
--,D.LABEL LABEL_NAME
,D.PATH
,D.FAILGROUP
,ROUND(D.OS_MB/1024) OS_GB
,ROUND(D.FREE_MB/1024) USABLE_GB
,D.MODE_STATUS ST_MODE
,D.FAILGROUP_TYPE
,RPAD(CASE WHEN D.VOTING_FILE = 'Y' THEN 'YES' ELSE 'no' END,6, ' ' ) VOTING
,D.STATE
,D.READS
,D.WRITES
,D.READ_TIME
,D.WRITE_TIME
,D.SECTOR_SIZE
,D.REDUNDANCY
,D.CREATE_DATE
,D.MOUNT_DATE
FROM V$ASM_DISK D
ORDER BY GROUP_NUMBER, DISK_NUMBER
/
PROMPT
PROMPT ===============> ASM FILES <===============
PROMPT
SELECT
A.GROUP_NUMBER
,( SELECT G.NAME FROM V$ASM_DISKGROUP G WHERE G.GROUP_NUMBER = A.GROUP_NUMBER ) GROUP_NAME
,A.FILE_NUMBER
,F.TYPE FILE_TYPE
,A.NAME FILE_NAME
,ROUND(F.BYTES/1024/1024) SIZE_MB
,ROUND(F.SPACE/1024/1024) SPACE_MB
,F.PERMISSIONS
,F.BLOCK_SIZE
,F.BLOCKS
,F.REDUNDANCY
,F.STRIPED
,F.CREATION_DATE
,F.MODIFICATION_DATE
,F.PRIMARY_REGION
,F.MIRROR_REGION
,A.ALIAS_INDEX
,A.ALIAS_INCARNATION
,A.SYSTEM_CREATED
,A.ALIAS_DIRECTORY
,A.FILE_INCARNATION
FROM V$ASM_FILE F
JOIN V$ASM_ALIAS A
ON ( A.GROUP_NUMBER = F.GROUP_NUMBER AND A.FILE_NUMBER = F.FILE_NUMBER AND A.FILE_INCARNATION = F.INCARNATION )
WHERE A.SYSTEM_CREATED = 'Y'
ORDER BY 1, 3
/
PROMPT
PROMPT ===============> ASM OPERATIONS <===============
PROMPT
SELECT * FROM GV$ASM_OPERATION;
PROMPT
SET LINES 300 PAGES 300
COL PATH FORMAT A30
COL LABEL FORMAT A30
SELECT GROUP_NUMBER, NAME, STATE FROM V$ASM_DISKGROUP
ORDER BY GROUP_NUMBER;
PROMPT
SET LINES 300 PAGES 300
COL LABEL_PATH FORMAT A40
SELECT
NVl(LABEL, 'PATH:' || PATH ) LABEL_PATH,
CREATE_DATE,
MOUNT_DATE,
DISK_NUMBER,
MOUNT_STATUS,
HEADER_STATUS,
MODE_STATUS,
STATE,
FAILGROUP_TYPE
FROM V$ASM_DISK
--WHERE GROUP_NUMBER = 0
ORDER BY GROUP_NUMBER, HEADER_STATUS;
