Scripts

TABLESPACE UTILIZATION

set lin 200 pages 500 feedback off
select avail_tname "TABLESPACE_NAME",sum(round(max_avail_space,2)) "MAX-ALLOCATION (GB)",
(round(alloc_avail_space,2)-round(free_space,2)) "USED (GB)",
((round(free_space,2))+round(max_avail_space,2)-round(alloc_avail_space,2)) "FREE (GB)",
(round(((round(alloc_avail_space,2)-round(free_space,2))/max_avail_space)*100,2)) "USED (%)",
(round((((round(free_space,2))+round(max_avail_space,2)-round(alloc_avail_space,2))/max_avail_space)*100,2)) "FREE (%)" from
(select avail_tname, sum(alloc_avail_space) alloc_avail_space, sum(max_avail_space) max_avail_space from
(select
tablespace_name avail_tname,
round(sum(BYTES)/1024/1024/1024,2) alloc_avail_space,
decode (autoextensible,'YES',round(sum(MAXBYTES)/1024/1024/1024, 2), round(sum(BYTES)/1024/1024/1024, 2)) max_avail_space,
autoextensible from dba_data_files group by tablespace_name, autoextensible)
group by avail_tname) a,
(select tablespace_name free_tname,sum(bytes)/1024/1024/1024 free_space from dba_free_space group by tablespace_name) b
where a.avail_tname=b.free_tname 
--
-- UNcomment below line to get ONLY permanent tablespaces above 90% usage only
-- 
-- and (round(((round(alloc_avail_space,2)-round(free_space,2))/max_avail_space)*100,2)) >= 90
group by avail_tname,alloc_avail_space,max_avail_space,free_space
UNION all
select avail_tname "TABLESPACE_NAME",sum(round(max_avail_space,2)) "MAX-ALLOCATION (GB)",
(round(alloc_avail_space,2)-round(free_space,2)) "USED (GB)",
((round(free_space,2))+round(max_avail_space,2)-round(alloc_avail_space,2)) "FREE (GB)",
(round(((round(alloc_avail_space,2)-round(free_space,2))/max_avail_space)*100,2)) "USED (%)",
(round((((round(free_space,2))+round(max_avail_space,2)-round(alloc_avail_space,2))/max_avail_space)*100,2)) "FREE (%)" from
(select avail_tname, sum(alloc_avail_space) alloc_avail_space, sum(max_avail_space) max_avail_space from
(select tablespace_name avail_tname, round(sum(BYTES)/1024/1024/1024,2) alloc_avail_space,
decode (autoextensible,'YES',round(sum(MAXBYTES)/1024/1024/1024, 2), round(sum(BYTES)/1024/1024/1024, 2)) max_avail_space,
autoextensible from dba_temp_files group by tablespace_name, autoextensible)
group by avail_tname) a,
(select tablespace_name free_tname,sum(bytes_free)/1024/1024/1024 free_space from v$temp_space_header group by tablespace_name) b
where a.avail_tname=b.free_tname
--
-- UNcomment below line to get ONLY temp tablespaces above 90% usage only
--
-- and (round(((round(alloc_avail_space,2)-round(free_space,2))/max_avail_space)*100,2)) >= 90
group by avail_tname,alloc_avail_space,max_avail_space,free_space
order by 5 desc;

Reference: Oracle Document

Smallfile tablespace(default)
Max datafiles/tempfiles => 1022
Max blocks in single datafile/tempfile => 2^22-1 = 4194303
Max datafile/tempfile size => 4194303 *  8kb = 33554424 kb [ 8kb db block size]

Bigfile
Max datafiles/tempfiles => 1
Max blocks in single datafile/tempfile => 2^32-1 = 4294967295
Max datafile/tempfile size => 4294967295 *  8kb = 34359738360 kb [ 8kb db block size]

Find Patching details on OEM 12c [12.1.05] for all targets:-

select a.HOST_NAME,
c.target_name,
a.HOME_NAME,
a.HOME_LOCATION,
a.COMPONENT_EXTERNAL_NAME,
a.COMPONENT_VERSION,
a.PATCH,
b.Installation_time
from
(
select HOST_NAME,
HOME_NAME,
HOME_LOCATION,
COMPONENT_EXTERNAL_NAME,
COMPONENT_VERSION,
MAX(PATCH_ID) PATCH,
SNAPSHOT_GUID
from
MGMT$SOFTWARE_COMPONENT_PATCH
where
component_name='oracle.rdbms' AND
HOST_NAME='co01mhf0025'
GROUP BY  HOST_NAME,
HOME_NAME,
HOME_LOCATION,
COMPONENT_EXTERNAL_NAME,
COMPONENT_VERSION,
SNAPSHOT_GUID
) a,
MGMT$APPLIED_PATCHES b,
mgmt$target_components c,
MGMT$OH_PATCH d
where 
a.host_name=b.HOST and
a.PATCH = b.PATCH  and
a.HOME_LOCATION=b.HOME_LOCATION and
a.patch=d.patch_id and 
a.host_name=d.host_name and
a.snapshot_guid=c.snapshot_guid(+) and
a.HOME_LOCATION=c.HOME_LOCATION(+) and
a.host_name=c.host_name(+)
ORDER BY 1,7;


Find disk usage of ASM

#########################################
#                                       #
# Name   : asmdu.bsh                     #
# Purpose: Find disk usage of ASM       #
#          directories                  #
# Usage  : Before executing this script #
#                                           #
#           ./asmdu.bsh DIRECTORY_NAME   #
#                                        #
#########################################
#!/bin/bash
#
# du of each subdirectory in a directory for ASM
#
D=$1

if [[ -z $D ]]
then
 echo "Please provide a directory !"
 exit 1
fi

(for DIR in `asmcmd ls ${D}`
 do
echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`
 done) | awk -v D="$D" ' BEGIN {  printf("\n\t\t%40s\n\n", D " subdirectories size")           ;
                 printf("%25s%16s%16s\n", "Subdir", "Used MB", "Mirror MB")   ;
                 printf("%25s%16s%16s\n", "------", "-------", "---------")   ;}
                 {
                 printf("%25s%16s%16s\n", $1, $2, $3)                         ;
                 use += $2                                                    ;
                 mir += $3                                                    ;
                 }
                 END   { printf("\n\n%25s%16s%16s\n", "------", "-------", "---------");
                 printf("%25s%16s%16s\n\n", "Total", use, mir)                 ;} '