[code language=”sql”]
#!/bin/ksh
############################################################################
# Script Name..:
# Description..:
# Author…….: Michael Culp
# Date………: / /2012
# Version……:
# Modified By..:
# Date Modified:
# Comments…..:
# Schema owner.:
# alter session set current
# Login User…:
# Run Order….:
# Dependent on.:
# Script type..: #
############################################################################
echo "ASM File types stored in this instance"
echo —————————————————–
sqlplus -s "/ as sysdba" <<EOF
set lines 120
set pagesize 66
column name format a25
column type format a20
select distinct type
from v\$asm_file;
EOF
echo "ASM files stored in ASM instance (better)"
echo —————————————————–
sqlplus -s "/ as sysdba" <<EOF
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 blocks FORMAT 9,999,999,999,999 HEAD ‘Blocks’
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)
, ‘<DIRECTORY>’) 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.blocks blocks
, 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;
echo "Amount of space left on individual ASM disk devices"
echo ———————————————————–
sqlplus -s "/ as sysdba" <<EOF
set pagesize 66
set lines 100
column path format a20
select path
, state
, total_mb
, free_mb
from v\$asm_disk;
EOF
echo "IO Stats ASM disks"
echo ———————————————————–
sqlplus -s "/ as sysdba" <<EOF
set pagesize 66
set lines 120
column path format a20
column read_time heading "Read Time" format 999,999.99
column write_time format 999,999.99
column writes format 999,999,999,999
column reads format 999,999,999,999
column bytes_written format 999,999,999,999
column bytes_read format 999,999,999,999
select path
,reads
,read_time
,bytes_read, writes
, write_time
, bytes_written
from v\$asm_disk;
echo "Amount of space left on individual ASM disk devices"
echo ———————————————————–
sqlplus -s "/ as sysdba" <<EOF
set pagesize 66
set lines 100
column path format a20
select path
,state
,total_mb
,free_mb
from v\$asm_disk;
EOF
echo "IO Stats ASM disks"
echo ———————————————————–
sqlplus -s "/ as sysdba" <<EOF
set pagesize 66
set lines 120
column path format a20
column read_time heading "Read Time" format 999,999.99
column write_time format 999,999.99
column writes format 999,999,999,999
column reads format 999,999,999,999
column bytes_written format 999,999,999,999
column bytes_read format 999,999,999,999
select path
, reads
, read_time
, bytes_read
, writes
, write_time
, bytes_written
from v\$asm_disk;
EOF
#!/bin/ksh
############################################################################
# Script Name..:
# Description..:
# Author…….: Michael Culp
# Date………: / /2012
# Version……:
# Modified By..:
# Date Modified:
# Comments…..:
# Schema owner.:
# alter session set current
# Login User…:
# Run Order….:
# Dependent on.:
# Script type..:
# ############################################################################
echo "ASM File types stored in this instance"
echo —————————————————–
sqlplus -s "/ as sysdba" >>EOF
set lines 120
set pagesize 66
column name format a25
column type format a20
select distinct type
from v\$asm_file;
EOF echo "ASM files stored in ASM instance (better)"
echo —————————————————–
sqlplus -s "/ as sysdba" <<EOF
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 blocks FORMAT 9,999,999,999,999 HEAD ‘Blocks’
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), ‘<DIRECTORY>’) 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.blocks blocks , 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; echo "Amount of space left on individual ASM disk devices" echo ———————————————————– sqlplus -s "/ as sysdba" <<EOF set pagesize 66 set lines 100 column path format a20 select path, state, total_mb, free_mb from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ———————————————————– sqlplus -s "/ as sysdba" <<EOF set pagesize 66 set lines 120 column path format a20 column read_time heading "Read Time" format 999,999.99 column write_time format 999,999.99 column writes format 999,999,999,999 column reads format 999,999,999,999 column bytes_written format 999,999,999,999 column bytes_read format 999,999,999,999 select path, reads, read_time, bytes_read, writes, write_time, bytes_written from v\$asm_disk; EOF 123,1 Bot echo "Amount of space left on individual ASM disk devices" echo ———————————————————– sqlplus -s "/ as sysdba" <<EOF set pagesize 66 set lines 100 column path format a20 select path, state, total_mb, free_mb from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ———————————————————– sqlplus -s "/ as sysdba" <<EOF set pagesize 66 set lines 120 column path format a20 column read_time heading "Read Time" format 999,999.99 column write_time format 999,999.99 column writes format 999,999,999,999 column reads format 999,999,999,999 column bytes_written format 999,999,999,999 column bytes_read format 999,999,999,999 select path, reads, read_time, bytes_read, writes, write_time, bytes_written from v\$asm_disk; EOF
[/code]